Syntax error on postgresql function. impossible syntax

Question

I try to correct this function but is impossible!! I declare one integer "var_id", and insert in id_val the value of frist query, if is null the tag name is inserted in a table and var_id = last insert id, otherwise do the last insert...

CREATE OR REPLACE FUNCTION public."InsertVideoTag"
(
 IN in_idvideo integer,
 IN in_tagname VARCHAR(25) 
)
RETURNS bigint AS
$$
DECLARE var_id bigint DEFAULT NULL;
SELECT var_id := IDTag FROM Tag WHERE TagName = in_tagname;
IF var_id IS NULL
THEN
INSERT INTO tag ( TagName )
VALUES( in_tagname );
var_id := SELECT CURRVAL(pg_get_serial_sequence('public.tag','idtag'));
END IF;
INSERT INTO video_has_tag 
( 
 IDVideo, 
 IDTag 
)
VALUES 
(
 in_idvideo, 
 var_id 
);
SELECT CURRVAL(pg_get_serial_sequence('public.video','idvideo'));
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

Answer 1

The function can be converted into a pure SQL one, which will make it much better performing one. Also I've noted, that the current functionality will create duplicate entries in the video_has_tag table if called multiple times with the same arguments. I've changed the function to be idempotent.

First table structure that I've assumed:

CREATE TABLE tag (
 idTag serial,
 tagName text);
CREATE TABLE video_has_tag (
 idVideo integer,
 idTag integer);

And then the function itself:

CREATE OR REPLACE FUNCTION insVideoTag(in_idvideo integer, in_tagname text)
RETURNS integer STRICT AS $insVideoTag$
WITH
new_tag AS (
 INSERT INTO tag (tagName)
 SELECT 2ドル
 WHERE NOT EXISTS (SELECT 1 FROM tag WHERE tagName = 2ドル)
 RETURNING idTag, tagName
), tag_data AS (
 SELECT * FROM new_tag
 UNION
 SELECT idTag, tagName FROM tag
 WHERE tagName = 2ドル
), new_target AS (
 INSERT INTO video_has_tag(idVideo, idTag)
 SELECT 1,ドル td.idTag
 FROM tag_data td
 WHERE NOT EXISTS (SELECT 1 FROM video_has_tag
 WHERE idVideo=1ドル AND idTag=td.idTag)
 RETURNING idVideo, idTag
)
SELECT idVideo FROM (
 SELECT * FROM new_target
 UNION
 SELECT * FROM video_has_tag
 WHERE idVideo=1ドル AND idTag=(SELECT idTag FROM tag_data)
) s;
$insVideoTag$ LANGUAGE sql;

Answer 2

• No need for if statements, a where clause is enough:
• selecting the current value is not suitable as a return value (or to be entered into an other tables FK) : it could have been bumped after the first insert.

---

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;
CREATE TABLE tag
 ( idtag SERIAL NOT NULL PRIMARY KEY
 , tagname varchar
 );
CREATE TABLE video_has_tag
 ( idvideo INTEGER NOT NULL
 , idtag INTEGER NOT NULL REFERENCES tag (idtag)
 );
CREATE OR REPLACE FUNCTION tmp.insertvideotag
 ( in_idvideo integer , in_tagname VARCHAR )
RETURNS bigint AS
$$
DECLARE var_id bigint DEFAULT NULL;
BEGIN
INSERT INTO tag (tagname )
SELECT in_tagname
WHERE NOT EXISTS (
 SELECT * FROM tag
 WHERE tagname = in_tagname
 );
INSERT INTO video_has_tag (idvideo,idtag)
SELECT in_idvideo, tg.idtag
FROM tag tg
WHERE tg.tagname = in_tagname
RETURNING idtag 
INTO var_id
 ;
RETURN var_id;
END;
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;
SELECT insertvideotag(11, 'Eleven');
SELECT insertvideotag(12, 'Eleven');
SELECT insertvideotag(13, 'Thirteen');
SELECT tv.idvideo
 ,tv.idtag, tg.tagname
FROM video_has_tag tv
JOIN tag tg ON tg.idtag = tv.idtag
 ;

Result:

NOTICE: CREATE TABLE will create implicit sequence "tag_idtag_seq" for serial column "tag.idtag"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tag_pkey" for table "tag"
CREATE TABLE
CREATE TABLE
CREATE FUNCTION
 insertvideotag 
----------------
 1
(1 row)
 insertvideotag 
----------------
 2
(1 row)
 idvideo | idtag | tagname 
---------+-------+----------
 11 | 1 | Eleven
 12 | 1 | Eleven
 13 | 2 | Thirteen
(2 rows)

• Clodoaldo Neto: The value returned by currval() is session specific. No problem in this regard.
• wildplasser: Does not look very trustable wrt another cornercase: ... for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.)

Answer 3

CREATE OR REPLACE FUNCTION public."InsertVideoTag"
(
 IN in_idvideo integer,
 IN in_tagname VARCHAR(25) 
)
RETURNS bigint AS
$$
DECLARE var_id bigint DEFAULT NULL;
begin
var_id := (select IDTag FROM Tag WHERE TagName = in_tagname);
IF var_id IS NULL
THEN
 INSERT INTO tag ( TagName )
 VALUES( in_tagname );
 var_id := (SELECT CURRVAL(pg_get_serial_sequence('public.tag','idtag')));
END IF;
INSERT INTO video_has_tag 
( 
 IDVideo, 
 IDTag 
)
VALUES 
(
 in_idvideo, 
 var_id 
);
return (SELECT CURRVAL(pg_get_serial_sequence('public.video','idvideo')));
end;
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

• Ihor Romanchenko: Will fail with error, if tag already exists (nextval wont be called and currval will fail). Maybe return var_id;?

Answer 4

I've found it's much easier to work with lower case columns & table names, etc. with postgres, so I've made a few changes to your existing code :

CREATE OR REPLACE FUNCTION public."insertvideotag"
(
 IN in_idvideo integer,
 IN in_tagname VARCHAR(25) 
)
RETURNS bigint AS
$$
DECLARE var_id bigint DEFAULT NULL;
BEGIN
--SELECT var_id := IDTag FROM Tag WHERE TagName = in_tagname;
SELECT idtag into var_id FROM tag WHERE tagname = in_tagname;
IF var_id IS NULL
THEN
INSERT INTO tag ( TagName )
VALUES( in_tagname );
--var_id := SELECT CURRVAL(pg_get_serial_sequence('public.tag','idtag'));
SELECT CURRVAL(pg_get_serial_sequence('public.tag','idtag')) into var_id;
END IF;
INSERT INTO video_has_tag 
( 
 idvideo, 
 idtag 
)
VALUES 
(
 in_idvideo, 
 var_id 
);
SELECT CURRVAL(pg_get_serial_sequence('public.video','idvideo'));
END
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

The remaining potential issues are the sequence names, and perhaps what value you'd like to return.