Fwd: concatenate and use as field

From: Alain Roger <raf(dot)news(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Fwd: concatenate and use as field
Date: 2009-03-29 11:58:30
Message-ID: 75645bbb0903290458r7eb56d05u49a4af02b40a888@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

>
>
> Hi,
>>
>> i have a character varying variable and i concatenate with some other
>> variable, using the '||' operator.
>> the result of this concatenation should be the name of a column in my
>> table.
>>
>> however i don't know how to tell that this new concatenated string is a
>> column name.
>> how to do it ?
>> thanks.
>>
>
> in fact i'm trying to do the following thing:
>
> select id, theme_ || $1 from themes;
>
> and based on the parameter my stored procedure has received, it should
> create something like that:
> select id, theme_eng from themes;
>
> if $1 = 'eng'
>

I think i'm a good way, but i still have a problem with the performance.
when i call my stored procedure, it can take 0.1 s to 3.5 seconds to execute
it on local computer.
How can i improve it ?

here is my stored procedure:
CREATE OR REPLACE FUNCTION sewe.get_category_and_amount(character varying)
RETURNS SETOF category_amount AS
$BODY$
DECLARE
inLanguage ALIAS FOR $1;
outCategoryAndAmount category_amount;

Lang character varying :='';
BEGIN
IF inLanguage = null OR inLanguage = '' THEN
Lang := 'eng';
ELSE
Lang := inLanguage;
END IF;

FOR outCategoryAndAmount IN
EXECUTE 'SELECT id, theme_name_' || Lang || ' FROM themes WHERE
parent_theme IS NULL ORDER BY theme_name_' || Lang || ' ASC'
LOOP
RETURN NEXT outCategoryAndAmount;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Black 2009-03-29 15:04:28 ALTER TABLE and adding FK Constraints - Assistance Requested
Previous Message Alain Roger 2009-03-29 11:41:44 Fwd: concatenate and use as field