Re: EXECUTE USING problem

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