On Thu, 11 Aug 2005, Ying Lu wrote:
> 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
