procedure string constant is parsed at procedure create time.

From: jian he <jian(dot)universality(at)gmail(dot)com>
To: pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: procedure string constant is parsed at procedure create time.
Date: 2023-11-07 11:52:20
Message-ID: CACJufxGQcEnMCT7yR8+FV4cd996UwGabYtxwqi7pNzq7tf8ZtQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

hi.
table "test" does not exist.

---1.this will work.
CREATE or replace PROCEDURE template_create() LANGUAGE SQL
AS $proc$ DROP TABLE if exists test cascade; CREATE TABLE test(id
int4range,valid_at tsrange,name text);$proc$;

----2.this will have errors.
CREATE or replace PROCEDURE template_create() LANGUAGE SQL AS $proc$
DROP TABLE if exists test cascade;
CREATE TABLE test(id int4range,valid_at tsrange,name text);
INSERT INTO test VALUES (NULL, tsrange('2018-01-01',
'2019-01-01'), 'null key');
$proc$;

----3.language plpgsql works fine.
CREATE or replace PROCEDURE template_create()
LANGUAGE plpgsql AS $proc$
begin
DROP TABLE if exists test101 cascade;
CREATE TABLE test101(id int4range,valid_at tsrange,name text);
INSERT INTO test101 VALUES (NULL, tsrange('2018-01-01',
'2019-01-01'), 'null key');
end
$proc$;

per manual: https://www.postgresql.org/docs/current/sql-createprocedure.html
"....This form only works for LANGUAGE SQL, the string constant form
works for all languages. This form is parsed at procedure definition
time, the string constant form is parsed at execution time...."

Why does the 2nd query fail? What am I missing?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2023-11-07 12:39:41 Re: procedure string constant is parsed at procedure create time.
Previous Message Merlin Moncure 2023-11-06 23:35:23 Re: Postgres Out Of Memory Crash