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

Passing column name to a function at runtime

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 (view raw or flat)
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?

Responses

pgsql-novice by date

Next:From: Oliver ElphickDate: 2007-09-12 13:47:42
Subject: Re: Passing column name to a function at runtime
Previous:From: Mija LeeDate: 2007-09-11 19:18:43
Subject: Re: problems loading languages

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