About using plpgsql funciton variable as the table name

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

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.

Look at: FOR-IN-EXECUTE

http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

--
Richard Huxton
Archonet Ltd

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