Thread: pl/pgSQL variable substitution
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
supportnamed 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@linz.govt.nz) and
destroythe original message.
LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ.
Thank you.
______________________________________________________________________________________________________
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@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
supportnamed 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@linz.govt.nz)
anddestroy 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
>
On 17 Feb 2011, at 5:33, Jeremy Palmer wrote: > 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. Your problem isn't with your table names, but with your parameter names. The "desc" parameter from your function declarationmatches the DESC keyword in your query, from the looks of it. Either use the old unnamed function declaration and use $1 and $2 in your function, or rename your parameters to somethingthat won't be in your queries as something else than a parameter reference. That said, I don't see where in your function you intend to use those parameters. Possibly you're trying to create a dynamicquery? Pavel answered that part of your question already. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4d5ccf3211731594261662!
Jeremy Palmer <JPalmer@linz.govt.nz> writes:
> 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
> ^
The other two responses seem to have missed the point, which is that
plpgsql is substituting for something that couldn't possibly be a
variable name. That is fixed as of 9.0. I'm not sure whether it
solves your issue completely, though. In general, using identifiers
that look like reserved words is painful --- and don't forget that
in plpgsql, you have *two* sets of reserved words to worry about,
the core SQL language's plus plpgsql's own.
regards, tom lane