插入如果不存在，请选择是否存在

Question

面临一些误解后GrestGresQL函数的工作方式。任务是检查我的DB中是否已经存在一些id。如果确实如此 - 从现有记录中返回一些数据。如果没有 - 插入一些数据并返回所有内容。

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;

选择返回错误：ERROR: invalid input syntax for integer: "id123"

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

将不胜感激。

Answer 1

通过各种表列传递多个参数，因为数组元素不是一个好主意。您可以最好使用复合类型作为参数。如本示例

所示，这有助于您甚至将多行数据传递到函数。

如果您坚持使用数组元素，则不能简单地在VALUES()中使用它们，必须使用索引

对它们进行引用

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

另一个问题是RETURNING *;它不会从函数返回。它只是返回查询的结果。如果您纯粹想将RETURNING A与子句一起使用。

假设表的定义是这样的，

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

您可以将函数的返回类型写为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;