Postgres Pro
Downloads
Home   >   mailing lists

Thread: creating a function with a variable table name

creating a function with a variable table name

From
George Nychis
Date:
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

Re: creating a function with a variable table name

From
"Joshua D. Drake"
Date:
> 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/

Re: creating a function with a variable table name

From
"A. Kretschmer"
Date:
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

Re: creating a function with a variable table name

From
George Nychis
Date:
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

Re: creating a function with a variable table name

From
"A. Kretschmer"
Date:
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