From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Graham <graham(at)gpmd(dot)co(dot)uk> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: EXECUTE USING problem |
Date: | 2011-11-15 13:18:57 |
Message-ID: | CAFj8pRBNzz4y7kY06SKYQgYwYD2-F9tAUpQsTaSfb1STFL2aXg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello
2011/11/15 Graham <graham(at)gpmd(dot)co(dot)uk>:
> 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 ?
>
you cannot to do it in plpgsql :(. Try to use PLPython or PLPerl
I found a working solution, but it is ugly - only plpgsql is just not
good language for this purpose. Maybe with HStore it can be done more
cleanly
CREATE or replace FUNCTION foo (tbl_name TEXT) RETURNS VOID
AS $$
DECLARE
r RECORD;
d TEXT;
BEGIN
FOR r IN
EXECUTE 'SELECT * FROM ' || tbl_name
LOOP
EXECUTE 'SELECT (''' || replace(r::text,'''','''''') || '''::' ||
tbl_name || ').descr' INTO d;
RAISE NOTICE '%', d;
END LOOP;
END;
$$ LANGUAGE plpgsql STRICT;
Regards
Pavel Stehule
> Thanks in advance.
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
From | Date | Subject | |
---|---|---|---|
Next Message | Raymond O'Donnell | 2011-11-15 13:26:47 | Re: EXECUTE USING problem |
Previous Message | Venkat Balaji | 2011-11-15 13:01:42 | Re: : Postgres installation error on CentOS |