Subject: Re: cannot create function that uses variable table name
From: Beth <bethg@cybernamix.com>
To: ddurst@larubber.com, pgsql-sql@postgresql.org
In-Reply-To: <39394.216.86.192.34.1043367202.squirrel@www.la-rubber.com>
References: <1043032653.2163.116.camel@white>
	<39394.216.86.192.34.1043367202.squirrel@www.la-rubber.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 24 Jan 2003 13:25:13 +1300
Message-Id: <1043367917.2253.92.camel@white>
Mime-Version: 1.0

Thanks for your reply David...

1)The "" round Retired are to label the column/field 'Retired' rather
than 'retired' (someone else created the database with Upper case
titles!)

2) Your code is correct.. cept that single quotes have to be escaped(?!)
so the following will do the trick when updating text fields...

CREATE FUNCTION temp(text,text,int4) RETURNS integer AS '
DECLARE
update_table ALIAS FOR $1;
update_field ALIAS FOR $2;
update_id ALIAS FOR $3;
BEGIN
EXECUTE ''UPDATE ''|| quote_ident(update_table) || '' SET "Retired" =
''''true'''' WHERE '' || quote_ident(update_field) || '' = '' ||
quote_literal(update_id);
RETURN update_id;
END;
' language 'plpgsql';

which creates...

and: select temp('TableName', 'TableID', 20);

returns 20.


On Fri, 2003-01-24 at 13:13, David Durst wrote:
> > I need sql functions to update the database. If I specify the filename
> > etc they work. BUT that leads to 6 functions which are exactly the same
> > apart from the file they update.
> >
> > 1) why can't I use a variable name and
> > 2) could someone please point me towards some examples of EXECUTE if
> > thats the only way to do it?
> >
> >
> > my example is:
> >
> 
> This should work
> 
> CREATE FUNCTION retire(varchar, varchar, int4) returns int4 AS'
>  DECLARE
>    varone ALIAS FOR $1;
>    vartwo ALIAS FOR $2;
>    varthr ALIAS FOR $3;
>  BEGIN
>  UPDATE varone SET "Retired" = 'true' WHERE vartwo = varthr;
>  SELECT (whatever to return the int4);
>  END;'
>  Language 'plpgsql';
> 
> 
> And I am not sure of the purpose for the "" around Retired