CODEWITHSUNDEEP
sqlpostgresql
TheNormalPerson
TheNormalPerson

Reputation: 591

Function dosn't return value from insert

I have this code: 

CREATE OR REPLACE FUNCTION get_create_tagId(tagName text) RETURNS text AS $$
BEGIN
        IF EXISTS(
           SELECT * FROM tags WHERE tag = tagName)
        THEN
            RETURN(SELECT id FROM tags WHERE tag=tagName);
        ELSE 
              INSERT INTO tags (tag) VALUES(tagName) RETURNING id;
        END IF ;
END
$$ LANGUAGE plpgsql;

But when i run it i get

ERROR: query has no destination for result data

even though i have RETURNING id What should i do/change?

Upvotes: 1

Views: 260

Answers (2)

user330315
user330315

Reputation:

You need to store the returned ID into a variable, then return that variable in the ELSE branch:

CREATE OR REPLACE FUNCTION get_create_tagid(p_tagnametext) 
  RETURNS text AS $$
DECLARE
  l_id integer;  
BEGIN
    IF EXISTS(SELECT * FROM tags WHERE tag = p_tagname)
    THEN
        RETURN (SELECT id FROM tags WHERE tag = p_tagname);
    ELSE 
       INSERT INTO tags (tag) VALUES(p_tagname) 
       RETURNING id
       INTO l_id;

       return l_id;
    END IF ;
END
$$ 
LANGUAGE plpgsql;

If you have a unique index (or constraint) on the tag column (which you really should have), then you can simplify this: 

with new_tag as (
  insert into tags (tag) 
  values ('one')
  on conflict do nothing
  returning id
)
select id
from new_tag
union all
select id
from tags
where tag = 'one';

The insert won't return anything if the tag exists and thus the final select * from new_tag won't return a row, but second part of the union will. If the row did not exists, the final select from tags wouldn't see it and return no row.

This will be more efficient and safe from race conditions.

Of course you can put that into a function too:

CREATE OR REPLACE FUNCTION get_create_tagid(p_tagname text) 
  RETURNS text AS $$
$$ 
  with new_tag as (
    insert into tags (tag) 
    values (p_tagname)
    on conflict do nothing
    returning id
  )
  select id
  from new_tag
  union all
  select id
  from tags
  where tag = p_tagname;
$$
LANGUAGE sql;

Upvotes: 3

Abdusoli
Abdusoli

Reputation: 659

You can declare new variable and get tag_id into it and return. I supposed that id is autoincrement or serial data type.

CREATE OR REPLACE FUNCTION get_create_tagId(tagName text) 
RETURNS text AS $$
DECLARE
tag_id INTEGER;

BEGIN
        IF EXISTS(SELECT * FROM tags WHERE tag = tagName) THEN

        tag_id := (SELECT id FROM tags WHERE tag=tagName);
        RETURN tag_id;

        ELSE 
              INSERT INTO tags(tag) VALUES(tagName);
              tag_id := (SELECT max(id) FROM tags);
              RETURN tag_id; 
        END IF;
END
$$ LANGUAGE plpgsql;

Upvotes: 0

Related Questions