Re: pgScript patch based on pgScript-1.0-beta-3

Hi Erikjan,

Here a few answers to your questions.

2008/8/14 Erikjan Rijkers <er@xs4all.nl>:
>  1) It would be nice if pgScript would only execute a select-block, like the
> normal pgAdmin querytool does.

Do you mean: redirecting a pgScript script to the regular query tool
if both the pgScript button is pressed and the edit window contains
only a select statement?
If this is case, I do not think it should be a good idea because
pgScript has its own behavior and it should not change whether there
is only one select statement or more in the edit window. Or maybe I
misunderstood your observation.

>  2) The example script from the url above says:
>
>   IF (SELECT 1 FROM table)   -- Then table exists
>
> which is only true if the table has rows, which of course initially it hasn't.
> This totally confounds the pgScript newbie ;-)  Maybe it can be changed to:
>
>   IF (SELECT count(*) FROM table)  -- Then table exists
>
>  Now it will NOT fail when table exist, but is empty.
>
>  This will make the snippet executable with pressing repeated F6.

Ok, it was a mistake in the documentation. I just tested this kind of
script and if the query returns nothing or it fails then the result is
considered false.
You're right, with a count(*) it returns something (0 if no rows) and
this is true.

>  3) When the BEGIN - END block fails, pgScript executes the ELSE block.  This
> seems questionable behaviour, no?  It can be seen with that same
> above-mentioned script. (in the original 'IF (SELECT 1 FROM table)' form )

I do not understand this observation. Could you be more precise please?

I just bring some clarity on my side:
1) BEGIN and END are like { and } in C++. They enclose blocks but they
have nothing to do with transactions
(http://pgscript.projects.postgresql.org/SCRIPT.html#id4731518).
You must use BEGIN TRANSACTION and END TRANSACTION for transactions
otherwise it conflicts with pgScript.
2) When a query fails (syntax error, ...) it does not interrupt the
script. In this case you have a WARNING with the error message. In the
case the query is fine you have a NOTICE.

This script outputs 'table exists' if the table t1 actually exists and
then exits because of an assertion failure, but it does not execute
the else block.

if (select count(*) from t1)
begin
    print 'table exists';
    assert 0;
end
else
begin
    print 'table does not exist';
end

In the "select count(*) from t1" in the above script is replaced with
a erroneous query "select count(*) fm t1" then you have a WARNING
because of the incorrect query and then it goes to the else block.
This is not really correct but except aborting the script I do not see
how I can do otherwise. Was this your observation?

Many thanks for your feedback,
Mickael