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
