Обсуждение: creating a function with a variable table name
Hey all,
I'm trying to create a function in which the table a query is run on is variable, but I
guess this is not as easy as I thought.
BEGIN
dp=> CREATE FUNCTION stats_addr_dst(date,text)
dp-> RETURNS setof addr_count
dp-> AS 'SELECT ip,sum(dst_packets)
dp'> FROM(
dp'> (SELECT dst_ip AS ip,sum(src_packets) AS dst_packets
dp'> FROM $2
dp'> WHERE interval=$1
dp'> GROUP BY dst_ip)
dp'> UNION ALL
dp'> (SELECT src_ip AS ip,sum(dst_packets) AS dst_packets
dp'> FROM $2
dp'> WHERE interval=$1
dp'> GROUP BY src_ip) )
dp'> AS topk
dp'> GROUP BY topk.ip
dp'> HAVING sum(dst_packets)>0
dp'> ORDER BY sum(dst_packets) DESC;'
dp-> LANGUAGE SQL;
ERROR: syntax error at or near "$2" at character 179
LINE 6: FROM $2
^
How can I pass the table name?
Thanks!
George
> dp'> HAVING sum(dst_packets)>0
> dp'> ORDER BY sum(dst_packets) DESC;'
> dp-> LANGUAGE SQL;
> ERROR: syntax error at or near "$2" at character 179
> LINE 6: FROM $2
> ^
> How can I pass the table name?
Look at the EXECUTE option in plpgsql.
Joshua D. Drake
>
> Thanks!
> George
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
am Thu, dem 01.03.2007, um 11:17:46 -0500 mailte George Nychis folgendes: > Hey all, > > I'm trying to create a function in which the table a query is run on is > variable, but I guess this is not as easy as I thought. > > BEGIN > dp=> CREATE FUNCTION stats_addr_dst(date,text) > ... > dp'> FROM $2 > ^ > How can I pass the table name? Build a string with your SQL and EXECUTE this string. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
do I need to use PREPARE with it also? A. Kretschmer wrote: > am Thu, dem 01.03.2007, um 11:17:46 -0500 mailte George Nychis folgendes: >> Hey all, >> >> I'm trying to create a function in which the table a query is run on is >> variable, but I guess this is not as easy as I thought. >> >> BEGIN >> dp=> CREATE FUNCTION stats_addr_dst(date,text) >> ... >> dp'> FROM $2 >> ^ >> How can I pass the table name? > > Build a string with your SQL and EXECUTE this string. > > > Andreas
am Thu, dem 01.03.2007, um 11:47:02 -0500 mailte George Nychis folgendes: > do I need to use PREPARE with it also? No. > > A. Kretschmer wrote: > >am Thu, dem 01.03.2007, um 11:17:46 -0500 mailte George Nychis folgendes: > >>Hey all, Please no top-posting with fullquote below your text. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net