Thread: plpgsql question - simple I would have thought

This should be simple, but it's not. I think I'm just missing something
simple.  I converted an oracle PL/SQL program to plpgssql, but it wouldn't
work, so I tried some very simple blocks, and even they fail. As far as I
can tell, the syntax is all valid.

Any help?


(postgres) robux:/opt/postgres/VRNJED
->cat it.sql

\echo raise notice attempt
begin
  raise notice 'hello';
end;

\echo select into attempt
declare
  i_int integer;
begin
 select count(1) into i_int from vrnsys_version;
end;

(postgres) robux:/opt/postgres/VRNJED
->psql -U vrnsys
Welcome to psql 7.3.2, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

VRNJED=> \i it.sql
raise notice attempt
psql:it.sql:4: ERROR:  parser: parse error at or near "raise" at character 9
psql:it.sql:5: WARNING:  COMMIT: no transaction in progress
COMMIT
select into attempt
psql:it.sql:9: ERROR:  parser: parse error at or near "integer" at character
17
psql:it.sql:11: ERROR:  parser: parse error at or near "select" at character
8
psql:it.sql:12: WARNING:  COMMIT: no transaction in progress
COMMIT
VRNJED-> \q
(postgres) robux:/opt/postgres/VRNJED
->

        Jed S. Walker
        Senior Database Administrator
        Comcast Media Centers
        Phone:    303.267.6759
        Email:     jed_walker@cable.comcast.com

        CONFIDENTIAL NOTICE
        This electronic mail transmission and any accompanying
documents contain information belonging to the sender, which may be
confidential and legally privileged.  If you are not the intended recipient,
any disclosure, copying, distribution or action taken in reliance on the
message is strictly prohibited.  If you have received this message in error,
please delete it immediately. Thank You

On Fri, Apr 01, 2005 at 02:52:20PM -0700, Walker, Jed S wrote:
>
> \echo raise notice attempt
> begin
>   raise notice 'hello';
> end;
>
> \echo select into attempt
> declare
>   i_int integer;
> begin
>  select count(1) into i_int from vrnsys_version;
> end;

PL/pgSQL is for writing user-defined functions, so you need some
CREATE FUNCTION statements.

http://www.postgresql.org/docs/7.3/interactive/plpgsql.html

> Welcome to psql 7.3.2, the PostgreSQL interactive terminal.

That version is pretty old -- if you must use 7.3 then at least
consider upgrading to 7.3.9.  See the Release Notes for a summary
of bug fixes and other changes:

http://www.postgresql.org/docs/7.3/interactive/release.html

If you're not committed to using 7.3 then you might want to check
out 8.0.2 when it's released (which should be Real Soon Now).

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/