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:
Show quoted text
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