Skip site navigation (1) Skip section navigation (2)

Re: Passing column name to a function at runtime

From: David Gardner <david(at)gardnerit(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Passing column name to a function at runtime
Date: 2007-09-12 14:08:22
Message-ID: 46E7F2D6.3000500@gardnerit.net (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Peter StowDate: 2007-09-14 05:54:44
Subject: Triggers: FROM Inserted
Previous:From: Oliver ElphickDate: 2007-09-12 13:47:42
Subject: Re: Passing column name to a function at runtime

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group