postgresql - 将选择值分配给变量到 PostgreSQL 9.3 函数中的执行

Question

考虑这样一个函数:

CREATE OR REPLACE FUNCTION public.foo(
    string1 character varying    
    )
  RETURNS integer AS
$BODY$
DECLARE
id1 INTEGER;

BEGIN

id1  := (SELECT id FROM mytable WHERE mycolumn = string1);
END;
......

它工作正常，我得到了用于函数其他部分的 id1 值。

现在，我想重写函数并传递一个充当表标识符的名称。

  CREATE OR REPLACE FUNCTION public.foo(
        string1 character varying,
        tablecode character varying --new argument
        )
      RETURNS integer AS
    $BODY$
    DECLARE
    id1 INTEGER;

    BEGIN
.....

现在，我尝试获取 id1 值和错误:

EXECUTE FORMAT('id1 := (SELECT id FROM %I WHERE mycolumn = %s)', tablecode||'_Conceptos', quote_literal(string1));

错误:

ERROR:  syntax error at or near "id1"
LINE 1: id1 := (SELECT id FROM "CENZANO_Conceptos" WHERE codigo ...
        ^
QUERY:  id1 := (SELECT id FROM "CENZANO_Conceptos" WHERE codigo = 'CENZANO')

--第二次尝试

 EXECUTE FORMAT ('SELECT %I.id INTO id1 FROM %I WHERE mycolumn = %s', tablecode||'_Conceptos',tablecode||'_Conceptos',quote_literal(string1));

END;
......

错误:

ERROR:  EXECUTE of SELECT ... INTO is not implemented
HINT:  You might want to use EXECUTE ... INTO or EXECUTE CREATE TABLE ... AS instead.

提前致谢

Answer 1

你可以使用INTO:

EXECUTE FORMAT('(SELECT id FROM %I WHERE mycolumn = %s)'
                , tablecode||'_Conceptos', quote_literal(string1)) INTO id1;