Thread: dynamic function question

I read the docs but I'm still a little lost as to how to do this.

I have this function which works fine.

CREATE OR REPLACE FUNCTION lookup_customer_byemail(varchar) RETURNS SETOF
customer_rec AS
'
DECLARE
   r RECORD;
   in_email ALIAS FOR $1;
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 cust_email ILIKE
in_email LOOP
   RETURN NEXT r;
   END LOOP;
   RETURN;
END '
LANGUAGE 'plpgsql';


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
   RETURN NEXT r;
   END LOOP;
   RETURN;
END '
LANGUAGE 'plpgsql';

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

Yes that works, thanks for the tip.

Chris
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: "Chris Ochs" <chris@paymentonline.com>
Cc: <pgsql-general@postgresql.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@postgresql.org
>