Thread: 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.
Attachment
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.
Attachment
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.