returning value from execute query string in plpgsql

Question

I'm trying to return an ID from a function like this:

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

But is not working.

How can I get this ID from sql query string executed?

I have never seen the Postgres error message But is not working. Seriously: please provide proper information with your questions. Always include your version of Postgres and always the verbatim error message if applicable. — Erwin Brandstetter
– Erwin Brandstetter, Commented Nov 11, 2016 at 1:17

Clodoaldo Neto · Accepted Answer · 2016-11-10 17:53:15Z

2

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;

Use %I format specifier to pass identifiers and execute using to pass data parameters.

edited Nov 10, 2016 at 17:53

answered Nov 10, 2016 at 17:24

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

Collectives™ on Stack Overflow

returning value from execute query string in plpgsql

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related