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 [email protected]) 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
