Assign select value to variable into Execute in PostgreSQL 9.3 function

Question

Consider a function like that:

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;
......

It works fine and I get the id1 value for using in other part of the function.

Now, I want to rewrite the function and pass a name who act like identifier of the table.

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

    BEGIN
.....

And now, there are my attepms for get id1 value and the errors:

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

Error:

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')

--second attepm

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

END;
......

Error:

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

Thanks in advance

Lukasz Szozda · Accepted Answer · 2018-09-08 18:16:00Z

1

You could use INTO:

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

answered Sep 8, 2018 at 18:16

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

exodehm Over a year ago

Thanks a lot. It works fine. I have a bunch of functions where I must to add the table like argument and it's being a headache for me ;-)

Collectives™ on Stack Overflow

Assign select value to variable into Execute in PostgreSQL 9.3 function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related