avoid replace of column with variable in pgsql function
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
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