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