From: | Ying Lu <ying_lu(at)cs(dot)concordia(dot)ca> |
---|---|
To: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: About using plpgsql funciton variable as the table |
Date: | 2005-08-11 18:05:55 |
Message-ID: | 42FB9383.8020609@cs.concordia.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thank you all for the helping.
I made a bit changes as the following and it works.
CREATE OR REPLACE FUNCTION update_code_map(VARCHAR, VARCHAR) RETURNS
VARCHAR AS $$
DECLARE
tableName ALIAS FOR $1;
st1_tabno_value ALIAS FOR $2;
rec_num INTEGER;
query_value VARCHAR;
myCountView RECORD;
BEGIN
query_value := 'SELECT COUNT(*) AS countNum FROM ' || tableName ;
FOR myCountView IN EXECUTE query_value LOOP
rec_num := myCountView.countNum;
END LOOP;
RAISE NOTICE 'There are % records in % table. ', rec_num,
tableName;
RETURN 'Populate ' || tableName || ' successfully!';
END;
$$ LANGUAGE plpgsql;
select update_code_map('code_map.dw_adm_dsn_map', '066');
- Emi
>
>
>>Greetings,
>>
>>I met a question about how to use *function variable *as the *table
>>name* to select count(*) into an integer variable for the table.
>>
>>
>>CREATE OR REPLACE FUNCTION update_code_map(VARCHAR, VARCHAR) RETURNS
>>VARCHAR AS $$
>>DECLARE
>> *tableName ALIAS FOR $1*;
>> *rec_num INTEGER*;
>> query_value VARCHAR;
>>BEGIN
>> -- way 1
>> query_value := 'SELECT *INTO rec_num* COUNT(*) FROM ' || tableName
>>|| ' ';
>> EXECUTE query_value;
>>
>> -- way 2
>> -- SELECT INTO rec_num COUNT(*) FROM tableName;
>> RAISE NOTICE 'There are % records in % table. ', rec_num,
>>tableName;
>>
>> RETURN 'Populate ' || tableName || ' successfully!';
>>END;
>>$$ LANGUAGE plpgsql;
>>select update_code_map('code_map.dw_adm_dsn_map', '066');
>>
>>
>>
>>I tried way 1 & way 2 , but neither of them worked. Could someone give
>>some clues?
>>
>>
>
>I think you can do it with FOR IN EXECUTE with a record variable.
>
>FOR rec IN EXECUTE <query string here> LOOP
> rec_num := rec.count;
>END LOOP
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Christian Goetze | 2005-08-11 18:10:38 | Upgrading 7.3.9 -> 7.4.8 - trouble with blobs |
Previous Message | Brandon Metcalf | 2005-08-11 17:51:29 | new Perl Server-Side Language in 8.0 |