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.