Обсуждение: About using plpgsql funciton variable as the table name

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?

Thanks a lot!
Emi

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

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

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
>
>