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
