avoid replace of column with variable in pgsql function

Started by Gerhard Heiftover 17 years ago2 messagesgeneral
Jump to latest
#1Gerhard Heift
ml-postgresql-20081012-3518@gheift.de

Hello,

I have a function with an out variable in which I have an INSERT
statement with the same name as column. Is it possible to avoid the
replacement?

Its something like this:

CREATE FUNCTION insert_foo(IN bar integer, OUT id integer) AS
$BODY$
BEGIN
INSERT INTO t1 (id, value) VALUES (bar, bar || '-bar');
SELECT foo INTO id FROM t2 WHERE value = bar;
END
$BODY$ LANGUAGE 'plpgsql';

In the INSERT statemet the id column is replaced with $2, which is not
what I want. Quoting the colum name does not help. And I dont want to
rename the output variable nor the column of the table.

Is this possible?

Regards,
Gerhard

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Gerhard Heift (#1)
Re: avoid replace of column with variable in pgsql function

Gerhard Heift <ml-postgresql-20081012-3518@gheift.de> writes:

I have a function with an out variable in which I have an INSERT
statement with the same name as column. Is it possible to avoid the
replacement?

No, but you could rename the variable. If you don't want to change the
externally exposed name, try using RENAME:

CREATE FUNCTION insert_foo(IN bar integer, OUT id integer) AS
$BODY$
declare rename id to _id;
BEGIN
INSERT INTO t1 (id, value) VALUES (bar, bar || '-bar');
SELECT id INTO _id FROM ...

regards, tom lane