[GENERAL] ALTER TABLE and adding FK Constraints - Assistance Requested

[Michael Black]

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.n...@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;