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

Maybe I don't understand your question, but why not filter your initial query to get only the records you want, instead of getting 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(at)postgresql(dot)org [mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Cheng
Sent: Friday, March 23, 2012 12:26 PM
To: pgsql-novice(at)postgresql(dot)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 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

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