Re: pl/pgSQL variable substitution
От | Pavel Stehule |
---|---|
Тема | Re: pl/pgSQL variable substitution |
Дата | |
Msg-id | AANLkTin60uuqWMuLNPc6qsgWmwgcOSG0QRaT-4FAMxAg@mail.gmail.com обсуждение исходный текст |
Ответ на | pl/pgSQL variable substitution (Jeremy Palmer <JPalmer@linz.govt.nz>) |
Список | pgsql-general |
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 >
В списке pgsql-general по дате отправления: