concatenate and use as field

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'

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;

First, I am relatively new to postgres, but have been using database (design not administering) for about 20 years (you would think that I could figure this out - lol). At an rate, I am trying to create tables that have forgein keys via a script. What happens is if the table that is referred to in the forgeing key does not exist, the table fails to create. Undertandable. So what I need to do is create all the tables and then go back and alter the tables by adding the forgein key constraint. I got that. But what I am looking for is the correct syntax to add the forgein key constrant. I have tried "ALTER TABLE <name> CONSTRANT <constraint description>" and "ALTER TABLE <name> ADD CONSTRANT <constraint description>". But both fail.

Yes I am being lazy. I should go through the script and create the tables that are referenced first then the ones with the forgein key. But I also need to know this in the even the schema changes in the future and more constratins are necessary. I have looked at the ALTER TABLE syntax on postgres but it refers back to the CREATE TABLE function.

Michael

Date: Sun, 29 Mar 2009 13:58:30 +0200
Subject: [GENERAL] Fwd: concatenate and use as field
From: raf.news@gmail.com
To: pgsql-general@postgresql.org

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;

On Mar 29, 2009, at 10:04 AM, Michael Black wrote:

First, I am relatively new to postgres, but have been using database
(design not administering) for about 20 years (you would think that
I could figure this out - lol). At an rate, I am trying to create
tables that have forgein keys via a script. What happens is if the
table that is referred to in the forgeing key does not exist, the
table fails to create. Undertandable. So what I need to do is
create all the tables and then go back and alter the tables by
adding the forgein key constraint. I got that. But what I am
looking for is the correct syntax to add the forgein key constrant.
I have tried "ALTER TABLE <name> CONSTRANT <constraint description>"
and "ALTER TABLE <name> ADD CONSTRANT <constraint description>".
But both fail.

Yes I am being lazy. I should go through the script and create the
tables that are referenced first then the ones with the forgein
key. But I also need to know this in the even the schema changes in
the future and more constratins are necessary. I have looked at the
ALTER TABLE syntax on postgres but it refers back to the CREATE
TABLE function.

Michael

Michael,

you are looking for this : http://www.postgresql.org/docs/current/static/sql-altertable.html

Ries

Thanks to all that responded. I got it figured out. The one I was testing did not have the associated table created yet (error message did not point me to a solution). Created the referenced table and it worked like a chump, er champ.

Michael

From: michaelblack75052@hotmail.com
To: pgsql-general@postgresql.org
Subject: [GENERAL] ALTER TABLE and adding FK Constraints - Assistance Requested
Date: Sun, 29 Mar 2009 15:04:28 +0000

First, I am relatively new to postgres, but have been using database (design not administering) for about 20 years (you would think that I could figure this out - lol). At an rate, I am trying to create tables that have forgein keys via a script. What happens is if the table that is referred to in the forgeing key does not exist, the table fails to create. Undertandable. So what I need to do is create all the tables and then go back and alter the tables by adding the forgein key constraint. I got that. But what I am looking for is the correct syntax to add the forgein key constrant. I have tried "ALTER TABLE <name> CONSTRANT <constraint description>" and "ALTER TABLE <name> ADD CONSTRANT <constraint description>". But both fail.

Yes I am being lazy. I should go through the script and create the tables that are referenced first then the ones with the forgein key. But I also need to know this in the even the schema changes in the future and more constratins are necessary. I have looked at the ALTER TABLE syntax on postgres but it refers back to the CREATE TABLE function.

Michael

Date: Sun, 29 Mar 2009 13:58:30 +0200
Subject: [GENERAL] Fwd: concatenate and use as field
From: raf.news@gmail.com
To: pgsql-general@postgresql.org

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;