Обсуждение: PgSQL not recognized

I just installed a fresh Postgres database. select version(); gives:

"PostgreSQL 8.1.5 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
3.4.2 (mingw-special)"

Normal statements like select * from sometable work fine.

I initiated the default databases, created the postgres user and I
tried to run the following query:

if 1 <> 1 then
   select 1
else
   select 2
end if;

The error was:

ERROR:  syntax error at or near "if" at character 1

I added the language using 'createlang plpgsql', but this didn't help.
This is similar to what I read from

http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html

I tried using the functions:

create function izitest() returns void as
'if 1 <> 1 then
   select 1
else
   select 2
end if;' language 'plpgsql'

where plpgsql is the name of the language I created. This gave the same
error:

ERROR:  syntax error at or near "if" at character 45

Statements like this are executing normally on other database engines.
I am obviously missing something, probably something specific to
Postgres. I couldn't find anything on the Net or documentation itself
after a few hours search that will give me a hint why this is not
working. Any ideas about this?

Anyway, to pose another question about this. The reason I need the
above is to check whether the table exists (from
information_schema.tables) and drop it only in that case (dirty
exception handling). There might be a way to do it (in 8.1) in another
way then using ifs to check. The other question still stands, however.

On Mon, Nov 13, 2006 at 03:08:18PM -0800, whytwelve13@yahoo.com wrote:
> I just installed a fresh Postgres database. select version(); gives:
>
> "PostgreSQL 8.1.5 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
> 3.4.2 (mingw-special)"
>
> Normal statements like select * from sometable work fine.

Have you read the documentation for the creation of functions? And
looked at the examples?

http://www.postgresql.org/docs/8.1/static/plpgsql.html
> I tried using the functions:
>
> create function izitest() returns void as
> 'if 1 <> 1 then
>    select 1
> else
>    select 2
> end if;' language 'plpgsql'
>
> where plpgsql is the name of the language I created. This gave the same
> error:

plpgsql is a language like oracles, you must have a BEGIN/END at least.
There's also the simpler 'sql' language, but it has no IF statement.

BTW, your statement is equivalent to:

SELECT CASE WHEN 1 <> 1 THEN 1 ELSE 2 END;

> Anyway, to pose another question about this. The reason I need the
> above is to check whether the table exists (from
> information_schema.tables) and drop it only in that case (dirty
> exception handling). There might be a way to do it (in 8.1) in another
> way then using ifs to check. The other question still stands, however.

In the latest release, in beta, you can DROP IF EXISTS. However, most
people just execute the drop and ignore the error, less risk of race
conditions.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

am  Mon, dem 13.11.2006, um 15:08:18 -0800 mailte whytwelve13@yahoo.com folgendes:
> I just installed a fresh Postgres database. select version(); gives:
>
> "PostgreSQL 8.1.5 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
> 3.4.2 (mingw-special)"
>
> Normal statements like select * from sometable work fine.
>
> I initiated the default databases, created the postgres user and I
> tried to run the following query:
>
> if 1 <> 1 then
>    select 1
> else
>    select 2
> end if;
>
> The error was:
>
> ERROR:  syntax error at or near "if" at character 1
>
> I added the language using 'createlang plpgsql', but this didn't help.
> This is similar to what I read from
>
> http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html
>
> I tried using the functions:
>
> create function izitest() returns void as
> 'if 1 <> 1 then
>    select 1
> else
>    select 2
> end if;' language 'plpgsql'
>
> where plpgsql is the name of the language I created. This gave the same
> error:

There are somethink wrong:

- wrong begin (i use the dollar-quoting-style)
- no 'begin'
- missing ';'
- you want to get a result but defined the function as void


I have rewritten this for you, i hope, this is what you expected:


create function izitest() returns int as $$
begin
if 1 <> 1 then
   return 1;
else
   return 2;
end if;
end;
$$ language 'plpgsql';


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

> Have you read the documentation for the creation of functions? And
> looked at the examples?
>
> http://www.postgresql.org/docs/8.1/static/plpgsql.html

Obviously not as good I should have. I missed the begin and the end,
adding those solved the problem. I am new to Postgres and it is quite
differently designed compared to what I have been using so far, I just
thought I could step right into it and do as I used to. Wrong. Even
minor things like the need for ; at the end of the statements can be
annoying until you find out what is the key. Reminds me of the old days
of switching from Basic to Pascal and C.

> In the latest release, in beta, you can DROP IF EXISTS. However, most
> people just execute the drop and ignore the error, less risk of race
> conditions.

I am using 8.1.5, which doesn't have IF EXISTS yet. Although, this is a
good addition to the language.

How do I ignore the error? The problem is that I would like to have a
set of drop/create statements executed at once and one drop failing
will abort the script execution. That is why I was searching for some
conditional execution statement within the query. Did you mean using
begin/exception for this or were you just talking about
one-statement-per-query execution and "forgetting" about any possible
exceptions?

Since you showed me how to properly use functions, it is not that hard
to make a function now that checks whether the table exists or not
(which I will probably do).

Nonetheless, can you use PL/PgSQL without building functions? This is
what I have been used to while using other SQL implementations. Not
related to the above problem - if a function can be built, then it can
accept the catalog, schema and table name and check whether the table
really exists using information_schema.tables. Just wondering, though,
is this something that Postgres is not designed for or am I missing
something that disallows me to run PL/PgSQL statements within "normal"
queries?

As I read through the documentation, I saw something like "PL/PgSQL is
installed automatically". Documentation seems huge (Postgres is itself
a huge system), though, I didn't have so much time to go through the
whole process. I am sure it is worth, but not when you only need it to
test something (which is the case here).

Thank you both for the help about this, it really matters to me!

On Tue, Nov 14, 2006 at 12:28:33PM -0800, whytwelve13@yahoo.com wrote:
> Obviously not as good I should have. I missed the begin and the end,
> adding those solved the problem. I am new to Postgres and it is quite
> differently designed compared to what I have been using so far, I just
> thought I could step right into it and do as I used to.

I'm not sure where you're coming from but AIUI plpgsql is modelled on
the oracle language. I've never used oracle like that so can't really
comment how that compares to any other database.

> How do I ignore the error? The problem is that I would like to have a
> set of drop/create statements executed at once and one drop failing
> will abort the script execution. That is why I was searching for some
> conditional execution statement within the query. Did you mean using
> begin/exception for this or were you just talking about
> one-statement-per-query execution and "forgetting" about any possible
> exceptions?

Yes, I was thinking of begin/exception. You just make the exception do
nothing. I've never really done this though, I've never had dymanic
schema's that way.

> Nonetheless, can you use PL/PgSQL without building functions?

No, people suggest it from time to time, but I don't beleive anyone has
submitted a complete proposal or patch for it.

> This is
> what I have been used to while using other SQL implementations. Not
> related to the above problem - if a function can be built, then it can
> accept the catalog, schema and table name and check whether the table
> really exists using information_schema.tables. Just wondering, though,
> is this something that Postgres is not designed for or am I missing
> something that disallows me to run PL/PgSQL statements within "normal"
> queries?

You're not missing anything, the only language the database understands
directly is SQL, other languages are restricted to use within
functions.

The documentation is large. There has been some effort to get it into a
form more accessable, but it's hard when there's that much of it.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

whytwelve13@yahoo.com wrote:
>
> I am using 8.1.5, which doesn't have IF EXISTS yet. Although, this is a
> good addition to the language.
>
> How do I ignore the error? The problem is that I would like to have a
> set of drop/create statements executed at once and one drop failing
> will abort the script execution.

Not exactly what you're looking for, but I just thought I'd check that
you know about the CREATE OR REPLACE ... form of most create statements.

--
   Richard Huxton
   Archonet Ltd

> I'm not sure where you're coming from but AIUI plpgsql is modelled on
> the oracle language. I've never used oracle like that so can't really
> comment how that compares to any other database.

MS SQL Server. I have been working on Oracle for a short time, but
never needed these.

> Yes, I was thinking of begin/exception. You just make the exception do
> nothing. I've never really done this though, I've never had dymanic
> schema's that way.

I read, though, that begin/exception is a thing to avoid when possible,
due to the overhead they create. Anyway, both of the things would solve
the problem.

> No, people suggest it from time to time, but I don't beleive anyone has
> submitted a complete proposal or patch for it.

...

> You're not missing anything, the only language the database understands
> directly is SQL, other languages are restricted to use within
> functions.
>
> The documentation is large. There has been some effort to get it into a
> form more accessable, but it's hard when there's that much of it.
>

I think it would be a good thing to allow non-pure-SQL languages to be
used. At least, a default language could be setup. As far as I
understand, PL/PgSQL just transfers whatever it doesn't understand to
the underlying pure-SQL interpreter, i.e. it's just a wrapper around
it.

This would help at least during "debugging" of the problems. When you
have to drop/create a function every time you need to change something,
can be annoying, especially if it's a small change you are making just
for testing. Or maybe I am just too lazy.

> Have a nice day,

You too, thanks for the comments and suggestions. Even though the news
were not too good, I know how the things stand now.