dynamic function question

From: "Chris Ochs" <chris(at)paymentonline(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: dynamic function question
Date: 2004-06-04 21:38:15
Message-ID: 064701c44a7c$3ec36750$250a8b0a@chris
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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';

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2004-06-04 21:50:05 Re: Building Thread-Safe on OS X
Previous Message Jan Wieck 2004-06-04 21:25:01 Re: [GENERAL] [HACKERS] Slony-I goes BETA