Обсуждение: EXECUTE USING problem

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.

Hello

2011/11/15 Graham <graham@gpmd.co.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On 15/11/2011 12:45, Graham wrote:
> 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;

I think that everything after EXECUTE needs to be a string. Also, USING
is part of an ORDER BY clause; so you'd do:

EXECUTE 'SELECT ($1)' || '.descr INTO d ORDER BY whatever USING
some_operator';

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie