Jeremy Palmer 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
>
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
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") || ' DE
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 $$
BE