Re: Passing column name to a function at runtime

On Wed, 2007-09-12 at 08:51 -0400, Wright, George wrote:
> I have a language table (translations) with column names which are the
> language for the items in that column:
>
> english spanish german etc.
>
> I am joining that table to another table on an integer id.
>
> I am trying to write a type statement and function where the name of
> the column is passed into the function. That language name appears in
> the select portion of the query:
>
> #type
>
> CREATE TYPE catCats AS (classid integer, varname text, color text,
> lang text);
>
> #function
>
> CREATE OR REPLACE FUNCTION listCategories(text) RETURNS SETOF catCats
> AS $$
> SELECT DISTINCT(classid), varname, color, $1
> FROM span_classes
> LEFT JOIN translations ON translations.lid
> span_classes.descript_tid
> GROUP BY classid, varname, color, $1
> ORDER BY classid ASC;
> $$ LANGUAGE SQL;

> #using the function
>
> select * from listCategories(english);
>
> If I pass in english or “english” it says column does not exist. If I
> pass in ‘english’ I get the literal english in my result set.
>
> If I substitute the literal english (with no quotes) in place of the
> $1 in the function, the query returns the english values from the
> translations table correctly.
>
> I’ve tried bytea for the datatype, as well as a table alias. I don’t
> know the language column until runtime so I can’t code it in the
> function.
>
> What am I doing wrong?

You cannot use a variable to denote the column name in an SQL function.
You need to make your function in plpgsql or plperl instead. Even then
you will have to build up your command as a string before using EXECUTE
(in plpgsql) to run it.

Alternatively you could keep your existing function but use a CASE
statement that would test the value of your variable in order to see
which column to report.

--
Oliver Elphick olly(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.