Re: table name in pl/pgsql

Hi,

Am Donnerstag, den 25.11.2004, 19:42 +0300 schrieb ON.KG:
> 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 need to build your query in your function
as a big string and pass it to EXECUTE

(See
http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
and
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
)

however encoding data into table names does not sound so elegant
for me - why not make it a real column?
The advantage would be you'd have a real query and let postgres
compile a plan for it - which is not possible for execute.

If you are concerned on index size you can always use partial
indices based on your "table number".

Regards
Tino