Re: dynamic function question

Yes that works, thanks for the tip.

Chris
----- Original Message -----
From: "Stephan Szabo" <sszabo(at)megazone(dot)bigpanda(dot)com>
To: "Chris Ochs" <chris(at)paymentonline(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Friday, June 04, 2004 3:06 PM
Subject: Re: [GENERAL] dynamic function question

> On Fri, 4 Jun 2004, Chris Ochs wrote:
>
> > What I would like to do is pass in the column name that is being queried
> > against (cust_email) so it can be dynamic. I tried the following but
it
> > always returns an empty set.
> >
> > CREATE OR REPLACE FUNCTION lookup_customer(varchar,varchar) RETURNS
SETOF
> > customer_rec AS
> > '
> > DECLARE
> > r RECORD;
> > in_string ALIAS FOR $1;
> > in_column ALIAS FOR $2;
> > BEGIN
> > FOR r IN SELECT
> >
s_oid,mer_id,site_id,order_id,pymt_type,pymt_subtype,comp_name,f_name1,l_nam
> > e1,f_name2,l_name2,address,city,state
> > ,zipcode,phone,cust_email,country from customers where in_column ILIKE
> > in_string LOOP
>
> I think you're going to need to look at using FOR IN EXECUTE to do this
> generating a query string, something like:
>
> FOR r IN EXECUTE ''SELECT s_oid,mer_id,site_id,order_id,pymt_type,
> pymt_subtype,comp_name,f_name1,l_name1,f_name2,l_name2,address,
> city,state,zipcode,phone,cust_email,country from customers
> where '' || in_column || '' ILIKE '' || in_string
> LOOP
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>