从plpgsql中执行查询字符串返回值

Question

我试图从这样的函数返回一个ID:

CREATE OR REPLACE FUNCTION fx (myTable text, myValue text) returns int
as $$
DECLARE
  sqlQuery TEXT;
  resultId INT;
BEGIN
  sqlQuery :=
    'INSERT INTO '
    || myTable || ' (id, some_column) values'
    || ' ('
    || 'nextval(' ||  quote_literal('seq_' || myTable) || ')'
    || ',' || myValue
    || ') RETURNING id INTO ' || resultId;
  EXECUTE sqlQuery;
  RETURN resultId;
END;
$$
LANGUAGE plpgsql;
select fx('some_table', 'some_value');

但是不工作。

我如何从sql查询字符串执行这个ID ?

Answer 1

create or replace function fx (mytable text, myvalue text)
returns int as $body$
declare
    sqlquery text;
    resultId int;
begin
    sqlquery := format ($$
        insert into %I (id, some_column) values ($1, $2)
        returning id
        $$, mytable
    );
    execute sqlquery
        into resultId
        using nextval('seq_' || mytable), myvalue;
    return resultId;
end;
$body$ language plpgsql;

使用%I format标识符传递标识符，使用execute using标识符传递数据参数