creating a function with a variable table name

From: George Nychis <gnychis(at)cmu(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: creating a function with a variable table name
Date: 2007-03-01 16:17:46
Message-ID: 45E6FCAA.7020107@cmu.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Hannes Dorbath 2007-03-01 16:18:44 Re: supporting 2000 simultaneous connections.
Previous Message Merlin Moncure 2007-03-01 16:09:56 Re: General Ledger db design