From: | Graham <graham(at)gpmd(dot)co(dot)uk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | EXECUTE USING problem |
Date: | 2011-11-15 12:45:08 |
Message-ID: | 4EC25ED4.6090802@gpmd.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Using PG 9.0.3, I wish to dynamically reference a column in a table
passed into a PL/PgSQL function as follows:
-- A table with some values.
DROP TABLE IF EXISTS table1;
CREATE TABLE table1 (
code INT,
descr TEXT
);
INSERT INTO table1 VALUES ('1','a');
INSERT INTO table1 VALUES ('2','b');
-- The function code.
DROP FUNCTION IF EXISTS foo (TEXT);
CREATE FUNCTION foo (tbl_name TEXT) RETURNS VOID
AS $$
DECLARE
r RECORD;
d TEXT;
BEGIN
FOR r IN
EXECUTE 'SELECT * FROM ' || tbl_name
LOOP
--SELECT r.descr INTO d; --IT WORK
EXECUTE 'SELECT ($1)' || '.descr' INTO d USING r; --DOES NOT WORK
RAISE NOTICE '%', d;
END LOOP;
END;
$$ LANGUAGE plpgsql STRICT;
-- Call foo function on table1
SELECT foo('table1');
Another post suggested EXECUTE 'SELECT $1::text::table1.descr' INTO d
USING r; but this does not work either. Can this be achieved currently?
what would be the syntax ?
Thanks in advance.
From | Date | Subject | |
---|---|---|---|
Next Message | Twaha Daudi | 2011-11-15 12:45:57 | PostgresSQL 8.4 to 9.0 on Windows 7 |
Previous Message | Alban Hertroys | 2011-11-15 12:26:19 | Re: : Postgres installation error on CentOS |