RETURN QUERY EXECUTE
was introduced with Postgres 8.4.
Your version is just too old and unsupported by now. Upgrade to a more recent version.
Also, dynamic column names in the result are very hard to come by. It's a principle of SQL that it wants to know the return type - including the names - up front.
Returning anonymous records without a column definition list only works for a single record. Else you have to provide a column definition list in the call. Details under his question on SO:
Return multiple fields as a record in PostgreSQL with PL/pgSQL
There are limited ways around this with with polymorphic types. Advanced stuff:
Refactor a PL/pgSQL function to return the output of various SELECT queries
BTW, your function would look like this in modern PL/pgSQL:
CREATE OR REPLACE FUNCTION get_policy_name (
_id int,
_lang text,
_def_value text
) RETURNS TABLE (col text) AS
$func$
BEGIN
RETURN QUERY EXECUTE
format('SELECT COALESCE(%I, col, $1)
FROM my_table WHERE id = $2'
, 'col_' || _lang)
USING _def_value, _id;
END
$func$ LANGUAGE plpgsql;
Call:
SELECT col AS col_fr FROM get_policy_name (1, 'fr', 'foo');
Here, I am simply using a column alias in the call to achieve what you want. Much easier than dynamic column names ...
DISTINCT ON()
Just as a side note, this is precisely what DISTINCT ON()
does (not to be confused with DISTINCT
)
SELECT DISTINCT ON ( expression [, ...] )
keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON
expressions are interpreted using the same rules as for ORDER BY
(see above). Note that the "first row" of each set is unpredictable unless ORDER BY
is used to ensure that the desired row appears first. For example
So if you were to write,
SELECT myFirstAgg(z)
FROM foo
GROUP BY x,y;
It's effectively
SELECT DISTINCT ON(x,y) z
FROM foo;
-- ORDER BY z;
In that it takes the first z
. There are two important differences,
You can also select other columns at no cost of further aggregation..
SELECT DISTINCT ON(x,y) z, k, r, t, v
FROM foo;
-- ORDER BY z, k, r, t, v;
Because there is no GROUP BY
you can not use (real) aggregates with it.
CREATE TABLE foo AS
SELECT * FROM ( VALUES
(1,2,3),
(1,2,4),
(1,2,5)
) AS t(x,y,z);
SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;
-- fails, as you should expect.
SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;
-- would not otherwise fail.
SELECT myFirstAgg(z), sum(z)
FROM foo
GROUP BY x,y;
Don't forget ORDER BY
Also, while I didn't bold it then I will now
Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example
Always use an ORDER BY
with DISTINCT ON
Using an Ordered-Set Aggregate Function
I imagine a lot of people are looking for first_value
, Ordered-Set Aggregate Functions. Just wanted to throw that out there. It would look like this, if the function existed:
SELECT a, b, first_value() WITHIN GROUP (ORDER BY z)
FROM foo
GROUP BY a,b;
But, alas you can do this.
SELECT a, b, percentile_disc(0) WITHIN GROUP (ORDER BY z)
FROM foo
GROUP BY a,b;
Best Answer
You manage to get it wrong in just too many ways. To begin with, there is no need for dynamic SQL at all.
The best way to "solve this problem" is probably to start with the basics. Read the excellent manual about
CREATE FUNCTION
, PL/pgSQL and SQL functions.A PL/pgSQL function could look like this (one of several variants):
Or, simpler as plain SQL function:
Or study some of the plpgsql examples that have been posted here.