Re: Three questions regarding PL/PGSQL

Jan,

> I assume you're trying to do something like
>
> CREATE FUNCTION myfunc(mytab.x%TYPE, mytab.y%TYPE) ...
>
> because that's the only way I've found to get this error

That's correct.

> Indeed, a good idea (for 7.2). Bruce, put it onto TODO
> please.

Thanks! I'm a little surprised that this hasn't come up before -- after
all, why did you include PLSQL-style %TYPE and %ROWTYPE declarations if
not for parameters?

> Can't reproduce that in 7.1(BETA). Could you send a little
> sql snippet reproducing the behaviour?

Sure, when I get home. I've a feeling that it's related to the Function
handler in 7.0.x storing functions as TEXT. What I'm trying to make
sure of is that it's not related to using PGAccess.

> Dunno what's exactly meant by that. Up to now we don't have
> savepoints and thus, anything done eventually in a PL/pgSQL
> trigger or function will allways roll back if a transaction
> get's aborted. Single statements (outside transaction block)
> have their own transaction, so nothing to worry about.

What I'm talking about is how, if an error occurs, the entire function
rolls back, not just a selected portion. I can't even include a BEGIN
TRANSACTION statment in a function; it errors out on compile. Nor can I
return a custom error message in place of a database error.

ALso, in other database engines, I've been able to use transactions to
prevent the interleaving of conflicting updates on the database server.
For example, I have some functions that insert a row into a table and
then report back the ID of the new row:

INSERT INTO clients ( ... ) VALUES ( ... );
SELECT CURRVAL(client_id) INTO new_client;

It's vitally important that another operation on the clients table does
not execute between the INSERT and the SELECT CURRVAL. It may be that
by creating transactions by default PGSQL functions are alredy doing
this; some reassurance on that count would be nice.

> Some sql examples would allways help.

More later when I get back to my PGSQL server.

> Getting better compile error messages (anything else than
> "parse error at or near ...") isn't easy in yacc/bison. Of
> course, the PL/pgSQL function handler does write some more as
> DEBUG messages to the Postmaster log. Unfortunately, these
> don't show up at the frontend side and cannot easily get
> turned into NOTICE ones because at that time the original
> ERROR has already been sent to the client and emitting
> NOTICE's then could confuse the fe/be protocol.

Hey, just the fact that you spit back "Error on Line 38" cuts my
debugging time in half over the SQL handler's "Error at or near ';'"

Of course, running a tail on the postmaster log helps, too ...

-Josh Berkus

--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco