From: Beth <bethg@cybernamix.com>
To: ddurst@larubber.com
To: pgsql-sql@postgresql.org
Subject: Re: cannot create function that uses variable table name
Date: 24 Jan 2003 13:25:13 +1300
Message-ID: <1043367917.2253.92.camel@white> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bethg@cybernamix.com, ddurst@larubber.com
Subject: Re: cannot create function that uses variable table name
In-Reply-To: <1043367917.2253.92.camel@white>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox