Re: pl/pgSQL variable substitution

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 declaration matches 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 something that 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 dynamic query? 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!