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;
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 |