pl/pgSQL variable substitution

[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