Thread: Functions
Ok, following on from the previous stuff, I'm now trying to put that update into a function. How can I reference the passed parameter(s)? CREATE FUNCTION fn_fubar(int4) AS 'UPDATE foo SET bar = tmp.numb FROM tmp WHERE tmp.numb = $$' LANGUAGSE 'sql'; where $$ would be the parameter passed to the function: SELECT fn_fubar(20); Simon Buying a SQL book this weekend :) -- "Linux - open doors, not windows." Simon Drabble It's like karma for your brain. simon@eskimo.com
I believe it's $1, $2, etc., so it would be: > CREATE FUNCTION fn_fubar(int4) AS > 'UPDATE foo SET bar = tmp.numb FROM tmp WHERE tmp.numb = $1' > LANGUAGSE 'sql'; --------------------------------------------------------------------- Andrew J. Perrin - aperrin@demog.berkeley.edu - NT/Unix Admin/Support Department of Demography - University of California at Berkeley 2232 Piedmont Avenue #2120 - Berkeley, California, 94720-2120 USA http://demog.berkeley.edu/~aperrin --------------------------SEIU1199 On Wed, 3 Nov 1999, Simon Drabble wrote: > Ok, following on from the previous stuff, I'm now trying to put that update > into a function. How can I reference the passed parameter(s)? > > CREATE FUNCTION fn_fubar(int4) AS > 'UPDATE foo SET bar = tmp.numb FROM tmp WHERE tmp.numb = $$' > LANGUAGSE 'sql'; > > where $$ would be the parameter passed to the function: > > SELECT fn_fubar(20); > > > Simon > Buying a SQL book this weekend :) > > > > -- > "Linux - open doors, not windows." > > Simon Drabble It's like karma for your brain. > simon@eskimo.com > > > ************ >
hi...
> CREATE FUNCTION fn_fubar(int4) AS
> 'UPDATE foo SET bar = tmp.numb FROM tmp WHERE tmp.numb = $$'
> LANGUAGSE 'sql';
i hate $1, $2 personally.. they make my eyes cross eventually (esp. once you
have 3 or 4 params.. ick) and they are hard to maintain in larger functions
(what was that $2 param again???)
so i usually use pl/pgsql and do this:
CREATE FUNCTION fn_fubar(int4) returns int4 AS '
DECLARE
myParam alias for $1;
BEGIN
UPDATE foo SET bar = tmp.numb FROM tmp WHERE tmp.numb = myParam;
return myParam;
END; '
LANGUAGSE 'pl/pgsql';
more typing, but easier to read and maintain later... but then, i'm picky.
--
Aaron J. Seigo
Sys Admin
On Wed, 3 Nov 1999, Aaron J. Seigo wrote: > hi... > > > CREATE FUNCTION fn_fubar(int4) AS > > 'UPDATE foo SET bar = tmp.numb FROM tmp WHERE tmp.numb = $$' > > LANGUAGSE 'sql'; > > i hate $1, $2 personally.. they make my eyes cross eventually (esp. once you > have 3 or 4 params.. ick) and they are hard to maintain in larger functions > (what was that $2 param again???) > > so i usually use pl/pgsql and do this: > > CREATE FUNCTION fn_fubar(int4) returns int4 AS ' > DECLARE > myParam alias for $1; > BEGIN > UPDATE foo SET bar = tmp.numb FROM tmp WHERE tmp.numb = myParam; > return myParam; > END; ' > LANGUAGSE 'pl/pgsql'; > > more typing, but easier to read and maintain later... but then, i'm picky. > > -- > Aaron J. Seigo > Sys Admin Ok, thanks, I must admit I wasn't really aware of pl/pgsql, so this gives me a reason to look into it. Simon. > -- "Linux - open doors, not windows." Simon Drabble It's like karma for your brain. simon@eskimo.com
On Wed, 3 Nov 1999, Andrew Perrin - Demography wrote: > I believe it's $1, $2, etc., so it would be: Hmmm...then I'm doing something else wrong, cos I'd tried that.. Oh well, thanks for the info. Simon. > > > CREATE FUNCTION fn_fubar(int4) AS > > 'UPDATE foo SET bar = tmp.numb FROM tmp WHERE tmp.numb = $1' > > LANGUAGSE 'sql'; > > > --------------------------------------------------------------------- > Andrew J. Perrin - aperrin@demog.berkeley.edu - NT/Unix Admin/Support > Department of Demography - University of California at Berkeley > 2232 Piedmont Avenue #2120 - Berkeley, California, 94720-2120 USA > http://demog.berkeley.edu/~aperrin --------------------------SEIU1199 > > On Wed, 3 Nov 1999, Simon Drabble wrote: > > > Ok, following on from the previous stuff, I'm now trying to put that update > > into a function. How can I reference the passed parameter(s)? > > > > CREATE FUNCTION fn_fubar(int4) AS > > 'UPDATE foo SET bar = tmp.numb FROM tmp WHERE tmp.numb = $$' > > LANGUAGSE 'sql'; > > > > where $$ would be the parameter passed to the function: > > > > SELECT fn_fubar(20); > > > > > > Simon > > Buying a SQL book this weekend :) > > > > > > > > -- > > "Linux - open doors, not windows." > > > > Simon Drabble It's like karma for your brain. > > simon@eskimo.com > > > > > > ************ > > > > > ************ > > -- "Linux - open doors, not windows." Simon Drabble It's like karma for your brain. simon@eskimo.com