Re: pl/pgSQL variable substitution

Hello

you cannot use a variable as column name or table name. It's not
possible, because it can change execution plan and it isn't allowed.

Use a dynamic SQL instead.

RETURN QUERY EXECUTE 'SELECT foo.bar, foo.' || quote_ident("desc") ||
' FROM foo ORDER BY foo.' || quote_ident("desc") || ' DESC'

Regards

Pavel Stehule

2011/2/17 Jeremy Palmer <JPalmer(at)linz(dot)govt(dot)nz>:
> Hi,
>
> I'm creating a pl/pgSQL function that returns a table that has a column name which is the same as a PostgreSQL reserved.  In the below example a have returning table with a column called 'desc':
>
> CREATE OR REPLACE FUNCTION bad_func() RETURNS TABLE (bar INTEGER, "desc" VARCHAR(100)) AS $$
> BEGIN
>    RETURN QUERY
>        SELECT foo.bar, foo."desc"
>        FROM foo
>        ORDER BY foo."desc" DESC;
> END;
> $$ LANGUAGE plpgsql;
>
> When I have a query that uses DESC reserved word within the function the following variable substitution occurs:
>
> ERROR:  syntax error at or near "$1"
> LINE 1:  SELECT foo.bar, foo."desc"   FROM foo."desc" ORDER BY  foo."desc"   $1
>                                                                             ^
>
> In my case I really would like to keep the table names i.e. no '_' etc.
>
> I can think of a few options to do this with varying levels of syntactic sugar:
>
> 1) Use RETURNS SETOF RECORD instead of RETURNS TABLE:
>
> CREATE OR REPLACE FUNCTION test1() RETURNS SETOF RECORD AS $$
> BEGIN
>    RETURN QUERY
>        SELECT
>            audit_id,
>            "desc"
>        FROM crs_sys_code
>        ORDER BY "desc" DESC ;
> END;
> $$ LANGUAGE plpgsql;
>
> Not a great interface because you have to declare the return record column names and types i.e.:
>
> SELECT * FROM test1() AS (id INTEGER, "desc" TEXT);
>
> 2) Create a composite type for the table row and use RETURNS SETOF:
>
> CREATE OR REPLACE FUNCTION test2() RETURNS SETOF table_type AS $$
> BEGIN
>    RETURN QUERY
>        SELECT
>            audit_id,
>            "desc"
>        FROM crs_sys_code
>        ORDER BY "desc" DESC;
> END;
> $$ LANGUAGE plpgsql;
>
> Better, but you have to create a type specifically for the function.
>
> 3) CREATE a SQL language wrapper around the example in 1):
>
> CREATE OR REPLACE FUNCTION test3() RETURNS TABLE (id INTEGER, "desc" TEXT) AS $$
>    SELECT * FROM test2() AS (id INTEGER, "desc" TEXT);
> $$ LANGUAGE sql;
>
> Nice interface, but now I need to manage two functions...
>
>
> Does anyone have any other suggestions here?
>
> Is this pl/pgSQL variable substitution seen as a feature of PostgreSQL, or a hangover from when PostgreSQL didn't support named function parameters? Really drives me crazy when naming variables in pl/pgSQL!
>
> Best Regards,
> Jeremy
> ______________________________________________________________________________________________________
>
> This message contains information, which is confidential and may be subject to legal privilege.
> If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message.
> If you have received this message in error, please notify us immediately (Phone 0800 665 463 or info(at)linz(dot)govt(dot)nz) and destroy the original message.
> LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ.
>
> Thank you.
> ______________________________________________________________________________________________________
>
> --
> 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
>