dynamic function question

[Chris Ochs]

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_subty pe,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_subty pe,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';
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Chris Ochs]

Yes that works, thanks for the tip.

Chris
----- Original Message -----
From: "Stephan Szabo" <ss****@megazone.bigpanda.com>
To: "Chris Ochs" <ch***@paymentonline.com>
Cc: <pg***********@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_subty pe,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_n ame2,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 ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings