function with tablename parameter

On Mon, 27 Oct 2003, Miso Hlavac wrote:

hello,

it is possible to write something similar???

create function get_count(varchar(32)) RETURNS int4 AS '
DECLARE
tmp int4;
BEGIN
SELECT COUNT(*) INTO tmp FROM $1;
RETURN tmp;
END;' LANGUAGE 'plpgsql';

Youll need to do something a little more complicated like:

create function get_count(varchar) RETURNS int8 AS '
DECLARE
tmp record;
BEGIN
FOR tmp IN EXECUTE ''SELECT COUNT(*) AS count FROM '' || $1 LOOP
RETURN tmp.count;
END LOOP;
END;'
LANGUAGE 'plpgsql';

To use dynamic queries you will probably have to use EXECUTE, take a look
here

http://www.postgresql.org/docs/7.3/interactive/plpgsql-statements.html#PLPGS
QL-STATEMENTS-EXECUTING-DYN-QUERIES

Although that page says that EXECUTE does not support SELECT INTO queries,
but you may be able to build something using FOR-IN-EXECUTE as described in
this section:

http://www.postgresql.org/docs/7.3/interactive/plpgsql-control-structures.ht
ml#PLPGSQL-RECORDS-ITERATING

HTH

Adam

hello,

it is possible to write something similar???

create function get_count(varchar(32)) RETURNS int4 AS '
DECLARE
tmp int4;
BEGIN
SELECT COUNT(*) INTO tmp FROM $1;
RETURN tmp;
END;' LANGUAGE 'plpgsql';

SELECT get_count('k_part');
SQL error:
ERROR: parser: parse error at or near "$1" at character 24

thanx, miso

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.