Postgres Pro
Downloads
Home   >   mailing lists

Thread: pl/pgsql or control structures outside of a function?

pl/pgsql or control structures outside of a function?

From
Peter Koczan
Date:
Hi all,

Is there any way to use PL/pgSQL code outside of a function?

The reason I'm asking is that I'm porting some code from
sybase/isql/SQR, and it allows some control code structures to be used
in an sql script. For instance,

begin if ((select count(*) from users where login = 'foo') = 0) begin   print 'Login "foo" does not exist.' end else
begin  print 'Adding account for "foo".'   insert into accounts values ('foo', 'bar') end
 
end

PL/pgSQL looks like it would make this port rather easy, but all the
docs and examples I found never had an example of PL/pgSQL outside of
a function. For the purposes of this port I'd really prefer not to
create functions for all this. I searched through the PL/pgSQL docs
and even several Google searches but couldn't find a definitive
answer. It's fine if the answer is no, I'm just curious if I should
pursue this path or look for a different one. And if there's a way to
do this or something like it besides "create scripts in
Perl/Python/etc." that you know of, I'd appreciate any information.

Thanks,
Peter

Re: pl/pgsql or control structures outside of a function?

From
John DeSoi
Date:
On Apr 3, 2009, at 5:03 PM, Peter Koczan wrote:

> Is there any way to use PL/pgSQL code outside of a function?

No.

>
>
> The reason I'm asking is that I'm porting some code from
> sybase/isql/SQR, and it allows some control code structures to be used
> in an sql script. For instance,

CASE might work for you.

http://www.postgresql.org/docs/8.3/interactive/functions-conditional.html




John DeSoi, Ph.D.

Re: pl/pgsql or control structures outside of a function?

From
Peter Koczan
Date:
On Fri, Apr 3, 2009 at 11:28 PM, John DeSoi <desoi@pgedit.com> wrote:
>> Is there any way to use PL/pgSQL code outside of a function?
>
> No.

I kinda figured, but it doesn't hurt to ask.

>> The reason I'm asking is that I'm porting some code from
>> sybase/isql/SQR, and it allows some control code structures to be used
>> in an sql script. For instance,
>
> CASE might work for you.
>
> http://www.postgresql.org/docs/8.3/interactive/functions-conditional.html

Thanks for the suggestion. I think that for what I'm trying to do,
adding a bunch of case statements would very quickly become
unmaintainable. Wrapper scripts are probably the way to go for me.

Thanks again,
Peter