From: | "Wright, George" <george(dot)wright(at)infimatic(dot)com> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Passing column name to a function at runtime |
Date: | 2007-09-12 12:51:01 |
Message-ID: | 336B1B69979ED3119E2A0060089777A3117C52A2@cmexch.cinmach.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Oliver Elphick | 2007-09-12 13:47:42 | Re: Passing column name to a function at runtime |
Previous Message | Mija Lee | 2007-09-11 19:18:43 | Re: problems loading languages |