data definition within plpgsql

Hi

po 28. 1. 2019 v 11:18 odesílatel <hamann.w@t-online.de> napsal:

Hi,

I tried this code (to be inserted within a larger psql script)

do $_$
declare
next int;
begin
select max(id) + 1 into next from items;
execute 'create temp sequence tmp_ids start $1' using next;

the parameter can be used only with statements with execution plan (DDL has
not execution plan).

you should to use execute format('create temp sequence tmp_ids start %s",
next);

Regards

Pavel Stehule

hamann.w@t-online.de wrote:

I tried this code (to be inserted within a larger psql script)

do $_$
declare
next int;
begin
select max(id) + 1 into next from items;
execute 'create temp sequence tmp_ids start $1' using next;
end
$_$ language plpgsql;

but that reports a syntax error near $1.
What is the proper way of doing that?

You cannot use parameters with DDL statements.

This should work:

EXECUTE format('CREATE TEMP SEQUENCE tmp_ids START %s', $1);

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com