Re: table name in pl/pgsql

hi,

ON.KG wrote:
> New question:
>
> i have tables like
> table_20041124,
> table_20041125,
> etc...
>
> i'm trying to make function (for example):
> =====================================
> CREATE FUNCTION get_count(text, text)
> RETURNS int2 AS '
> DECLARE
> cnt int4;
> BEGIN
> SELECT INTO cnt COUNT(*)
> FROM table_$1 -- That doesn't work
> WHERE key = $2;
>
> RETURN cnt;
> END;'
> LANGUAGE 'plpgsql';
> =====================================
>
> call this function by:
>
> =====================================
> SELECT get_count("20041124", "something");
> =====================================
>
> string in funstion - FROM table_$1
>
> how could i get a final correct table name here?

You can use execute for dynamic sql.
CREATE FUNCTION get_count(text, text) RETURNS int2 AS '
declare rec record;
begin
for rec in execute ''select COUNT(*) as num from table_''||$1||''
where key=''''||$2'''' '';
loop
return rec.num;
end loop;
return;
end;

PS: anyway, you want returns int2 , but you declared int4 :)

C.