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
Show quoted text
e1,f_name2,l_name2,address,city,state
,zipcode,phone,cust_email,country from customers where in_column ILIKE
in_string LOOPI 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