Thread: Define a function that accepts query result as a parameter

Hi all,

I have two tables A and B, I defined a function f() on A. The problem is
to process the rows in A whose id has appeared in Table B. To save
time,  I want to to run function f() on only records that appeared in B.
I tried this:

define f(integer[]) as
$$
process records where id in $1
$$ language sql

then I tried to call the function using f(select array_agg(id) from B).
It does not work.
Do you guys know how I can achieve this?

Thanks,
Cheng

Maybe I don't understand your question, but why not filter your initial query to get only the records you want, instead
ofgetting all seventeen million records in table A when there's only ten of them that have IDs in table B? 

SELECT a.* FROM a
INNER JOIN b on b.id = a.id

Or something like that.

RobR

-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Cheng
Sent: Friday, March 23, 2012 12:26 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Define a function that accepts query result as a parameter

Hi all,

I have two tables A and B, I defined a function f() on A. The problem is to process the rows in A whose id has appeared
inTable B. To save time,  I want to to run function f() on only records that appeared in B.  
I tried this:

define f(integer[]) as
$$
process records where id in $1
$$ language sql

then I tried to call the function using f(select array_agg(id) from B).
It does not work.
Do you guys know how I can achieve this?

Thanks,
Cheng

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice