Thread: 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@lfix.co.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.

What you could do instead is make a language table, so that you could do:
SELECT lanugage_name, language_id FROM language_tbl WHERE language_name=$1;

Then you could join it on your other table.

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?
>


--
David Gardner