Insert IF NOT EXIST, select IF EXIST

Question

Faced some misunderstanding on how does Postgresql functions works. The task is to check if some id already exists in my DB. If it does - return some data from existing recording. If doesn't - insert some data and return everything back.

CREATE OR REPLACE FUNCTION test(_id varchar, _data varchar[]) RETURNS varchar[] AS $$
BEGIN
    IF EXISTS (SELECT 1 FROM my_table WHERE id = _id) THEN
        SELECT * FROM my_table WHERE id = _id;
    ELSE
        INSERT INTO my_table (id, filename, size) VALUES (_data) RETURNING *;
    END IF;
END
$$ LANGUAGE plpgsql;

Select returns an error: ERROR: invalid input syntax for integer: "id123"

SELECT test('id123', ARRAY['id123', 'filename', 123456]);

Would be appreciated for any help.

Well, obviously `id123` is not a valid integer value - what exactly is your question? — , Jul 03 '19 at 14:55
Also, your function as written will not return varchar[], so that will also throw an error. — Jeremy, Jul 03 '19 at 14:56

score 0 · Answer 1 · answered Jul 03 '19 at 15:25

Passing multiple arguments for various table columns as array elements is not a good idea. You may better use a Composite Type as argument. This helps you to even pass multiple rows of data to functions as shown in this example

If you insist on using array elements, you can't simply use them within VALUES(), they have to be referenced using the index

INSERT INTO my_table  (id, filename, size) 
                      VALUES (_data[1],_data[2],_data[3]::int );

Another problem is with RETURNING *; It does not return from a function. It just returns the result of the query. If you purely want to use RETURNING a with clause could be used.

Assuming that the the definition of your table is something like this,

                    Table "public.my_table"
  Column  |       Type        | Collation | Nullable | Default
----------+-------------------+-----------+----------+---------
 id       | character varying |           |          |
 filename | text              |           |          |
 size     | integer           |           |          |

you may write your function's return type as a TABLE

CREATE OR REPLACE FUNCTION test(_id varchar, _data varchar[]) 
 RETURNS TABLE (id varchar, filename text, size int)
AS $$
BEGIN
 IF NOT EXISTS ( SELECT 1 FROM my_table t WHERE t.id = _id) THEN
   RETURN QUERY
   with  ins AS 
    ( 
      INSERT INTO my_table  (id, filename, size) 
                      VALUES (_data[1],_data[2],_data[3]::int ) RETURNING *
     )                    ---refer elements             ^ properly cast them
    SELECT * FROM ins;   
    ELSE
     RETURN QUERY SELECT * FROM my_table t WHERE t.id = _id; 

    END IF;
END
$$ LANGUAGE plpgsql;

Insert IF NOT EXIST, select IF EXIST

1 Answers1