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@xxxxxxxxxxxx>:
> 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@xxxxxxxxxxxx) 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@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
