Re: 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