pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
anonymous block in Postgres - Hello World
4+ messages / 3 participants
[nested] [flat]

* anonymous block in Postgres - Hello World
@ 2007-05-24 12:30 Abraham, Danny <danny_abraham@bmc.com>
  2007-05-24 15:12 ` Re: anonymous block in Postgres - Hello World Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Abraham, Danny @ 2007-05-24 12:30 UTC (permalink / raw)
  To: pgsql-admin; +Cc: Abraham, Danny <danny_abraham@bmc.com>; Devrim GÜNDÜZ <devrim@gunduz.org>

#!/bin/sh

psql --quiet  -t << EOF

declare

   x varchar(80);

begin

   x:='Hello World';

   raise notice x;

end;

\q

EOF

 

====== 

 

Hi,

 

This code is my first "like Oracle anonymous blocl". It does not go through.

 

Can anyone help?

 

Thanks

 

Danny

 

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: anonymous block in Postgres - Hello World
  2007-05-24 12:30 anonymous block in Postgres - Hello World Abraham, Danny <danny_abraham@bmc.com>
@ 2007-05-24 15:12 ` Tom Lane <tgl@sss.pgh.pa.us>
  2007-05-24 15:31   ` Re: anonymous block in Postgres - Hello World Igor Neyman <ineyman@perceptron.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Tom Lane @ 2007-05-24 15:12 UTC (permalink / raw)
  To: Abraham, Danny <danny_abraham@bmc.com>; +Cc: pgsql-admin@postgresql.org, Devrim GÜNDÜZ <devrim@gunduz.org>

"Abraham, Danny" <danny_abraham@bmc.com> writes:
> This code is my first "like Oracle anonymous blocl". It does not go =
> through.

There are no anonymous blocks in Postgres --- you must create a
function.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: anonymous block in Postgres - Hello World
  2007-05-24 12:30 anonymous block in Postgres - Hello World Abraham, Danny <danny_abraham@bmc.com>
  2007-05-24 15:12 ` Re: anonymous block in Postgres - Hello World Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-05-24 15:31   ` Igor Neyman <ineyman@perceptron.com>
  2007-05-24 15:57     ` Re: anonymous block in Postgres - Hello World Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Igor Neyman @ 2007-05-24 15:31 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Abraham, Danny <danny_abraham@bmc.com>; +Cc: pgsql-admin; Devrim GÜNDÜZ <devrim@gunduz.org>

Coming from Oracle world, I also was missing the ability to execute anonymous blocks.
So I wrote this function:

CREATE OR REPLACE FUNCTION exec_plpgsql_block(exec_string text)
RETURNS BOOLEAN
AS $THIS$
DECLARE lRet BOOLEAN;
BEGIN
EXECUTE 'CREATE OR REPLACE FUNCTION any_block()
RETURNS VOID
AS $$ ' || exec_string || ' $$LANGUAGE PLPGSQL;' ;
PERFORM any_block();
RETURN TRUE;
END;
$THIS$LANGUAGE PLPGSQL;

to which I pass my "anonymous" block as a parameter.
As you can see, this function creates/replaces "on the fly" function "any_block()" and executes it.
Pretty simple solution.

Igor Neyman


-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Thursday, May 24, 2007 11:12 AM
To: Abraham, Danny
Cc: pgsql-admin@postgresql.org; Devrim GÜNDÜZ
Subject: Re: [ADMIN] anonymous block in Postgres - Hello World 

"Abraham, Danny" <danny_abraham@bmc.com> writes:
> This code is my first "like Oracle anonymous blocl". It does not go = 
> through.

There are no anonymous blocks in Postgres --- you must create a function.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: anonymous block in Postgres - Hello World
  2007-05-24 12:30 anonymous block in Postgres - Hello World Abraham, Danny <danny_abraham@bmc.com>
  2007-05-24 15:12 ` Re: anonymous block in Postgres - Hello World Tom Lane <tgl@sss.pgh.pa.us>
  2007-05-24 15:31   ` Re: anonymous block in Postgres - Hello World Igor Neyman <ineyman@perceptron.com>
@ 2007-05-24 15:57     ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2007-05-24 15:57 UTC (permalink / raw)
  To: Igor Neyman <ineyman@perceptron.com>; +Cc: Abraham, Danny <danny_abraham@bmc.com>; pgsql-admin@postgresql.org, Devrim GÜNDÜZ <devrim@gunduz.org>

"Igor Neyman" <ineyman@perceptron.com> writes:
> CREATE OR REPLACE FUNCTION exec_plpgsql_block(exec_string text)
> RETURNS BOOLEAN
> AS $THIS$
> DECLARE lRet BOOLEAN;
> BEGIN
> EXECUTE 'CREATE OR REPLACE FUNCTION any_block()
> RETURNS VOID
> AS $$ ' || exec_string || ' $$LANGUAGE PLPGSQL;' ;
> PERFORM any_block();
> RETURN TRUE;
> END;
> $THIS$LANGUAGE PLPGSQL;

That hasn't failed for you yet?  It will the first time you use $$
in the argument.

Use quote_literal() please ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2007-05-24 15:57 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-24 12:30 anonymous block in Postgres - Hello World Abraham, Danny <danny_abraham@bmc.com>
2007-05-24 15:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-24 15:31   ` Igor Neyman <ineyman@perceptron.com>
2007-05-24 15:57     ` Tom Lane <tgl@sss.pgh.pa.us>

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