Cross-posting (was Re: PL/pgSQL examples NOT involving functions)
Cross-posting (was Re: PL/pgSQL examples NOT involving functions)
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Re: PL/pgSQL examples NOT involving functions
От:
"Aasmund Midttun Godal" <postgresql@envisity.com>
Дата:
Well, to the best of my knowledge your question is a contradiction in terms: plpgsql is a procedural language to use it you must create a stored procedure or function... However you are probably able to do many of the things you may want with plain old SQL look it up in the docs especially the case structure. Regards, Aasmund. On 15 Nov 2001 11:49:12 -0500, Roland Roberts wrote: > How can I write a few lines of PL/pgSQL which do not involve creating > a function? I can find no examples of this in the docs, but say I > would like to do something like > > BEGIN > IF EXISTS (SELECT * FROM foo WHERE idx = 27) > THEN > UPDATE foo SET var='some value' WHERE idx=27; > ELSE > INSERT INTO foo (idx, var) VALUES (27, 'some value'); > END IF > END; > > roland > -- > PGP Key ID: 66 BC 3B CD > Roland B. Roberts, PhD RL Enterprises > roland@rlenter.com 76-15 113th Street, Apt 3B > roland@astrofoto.org Forest Hills, NY 11375 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46
Re: PL/pgSQL examples NOT involving functions
От:
"Aasmund Midttun Godal" <postgresql@envisity.com>
Дата:
On 18 Nov 2001 22:57:05 -0500, Roland Roberts wrote: > > Aasmund> Well, to the best of my knowledge your question is a > Aasmund> contradiction in terms: plpgsql is a procedural language > Aasmund> to use it you must create a stored procedure or > Aasmund> function... > > Coming from an Oracle background, perhaps I don't see it the same > way. To merely *call* a PL/SQL procedure, I have to do something like > > BEGIN > MYPROC (a, b, c) > END; > > So I *could* create a pl/pgsql function and call it like that, but > since pl/pgsql procedures aren't compiled I wondered if I could do it > inline. I do believe they are compiled. > > Aasmund> However you are probably able to do many of the things > Aasmund> you may want with plain old SQL look it up in the docs > Aasmund> especially the case structure. > > Thanks for this suggestion; I'll have a look at the case structure.... > > roland > -- > PGP Key ID: 66 BC 3B CD > Roland B. Roberts, PhD RL Enterprises > roland@rlenter.com 76-15 113th Street, Apt 3B > roland@astrofoto.org Forest Hills, NY 11375 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46
Re: PL/pgSQL examples NOT involving functions
От:
"Aasmund Midttun Godal" <postgresql@envisity.com>
Дата:
Re: PL/pgSQL examples NOT involving functions
От:
"Aasmund Midttun Godal" <postgresql@envisity.com>
Дата:
Re: PL/pgSQL examples NOT involving functions
От:
"Aasmund Midttun Godal" <postgresql@envisity.com>
Дата:
Re: PL/pgSQL examples NOT involving functions
От:
"Aasmund Midttun Godal" <postgresql@envisity.com>
Дата:
Re: PL/pgSQL examples NOT involving functions
От:
"Aasmund Midttun Godal" <postgresql@envisity.com>
Дата:
Re: [DOCS] PL/pgSQL examples NOT involving functions
От:
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Дата:
> > How can I write a few lines of PL/pgSQL which do not involve creating > > a function? I can find no examples of this in the docs, but say I > > would like to do something like > > > > BEGIN > > IF EXISTS (SELECT * FROM foo WHERE idx = 27) > > THEN > > UPDATE foo SET var='some value' WHERE idx=27; > > ELSE > > INSERT INTO foo (idx, var) VALUES (27, 'some value'); > > END IF > > END; Interesting. I see you are trying to simulate MySQL's 'REPLACE INTO' syntax. On an aside, I was recently asked to convert this MySQL code to PGSQL code for the GeekLog project. I gave up: REPLACE INTO $table ($fields) SELECT $values FROM $tablefrom; Evil! Chris
PL/pgSQL examples NOT involving functions
От:
Roland Roberts <roland@astrofoto.org>
Дата:
How can I write a few lines of PL/pgSQL which do not involve creating
a function? I can find no examples of this in the docs, but say I
would like to do something like
BEGIN
IF EXISTS (SELECT * FROM foo WHERE idx = 27)
THEN
UPDATE foo SET var='some value' WHERE idx=27;
ELSE
INSERT INTO foo (idx, var) VALUES (27, 'some value');
END IF
END;
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375
Re: PL/pgSQL examples NOT involving functions
От:
Roland Roberts <roland@astrofoto.org>
Дата:
>>>>> "Aasmund" == Aasmund Midttun Godal writes:
Aasmund> Well, to the best of my knowledge your question is a
Aasmund> contradiction in terms: plpgsql is a procedural language
Aasmund> to use it you must create a stored procedure or
Aasmund> function...
Coming from an Oracle background, perhaps I don't see it the same
way. To merely *call* a PL/SQL procedure, I have to do something like
BEGIN
MYPROC (a, b, c)
END;
So I *could* create a pl/pgsql function and call it like that, but
since pl/pgsql procedures aren't compiled I wondered if I could do it
inline.
Aasmund> However you are probably able to do many of the things
Aasmund> you may want with plain old SQL look it up in the docs
Aasmund> especially the case structure.
Thanks for this suggestion; I'll have a look at the case structure....
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375
