Thread: function with tablename parameter

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

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.