Postgres Pro
Downloads
Home   >   mailing lists

Thread: PREPARE vs query with MULTIPLE statements

PREPARE vs query with MULTIPLE statements

From
Andrei Kovalevski
Date:
Hello.

I have found an interesting FRONTEND/BACKEND protocol behaviour. Let's
consider following query:
"SELECT 1; SELECT 2; SELECT3; SELECT4;"

1) If I send it as a simple query - I'm getting:
- correct results for SELECT 1; SELECT 2; SELECT3; SELECT4;
- and then one ReadyForQuery response
from backend.
[send_simple.log & recv_simple.log]

2) If I send it as 'PREPARE "SQL_CUR1" AS SELECT 1; SELECT 2; SELECT3;
SELECT4;' and then 'EXECUTE "SQL_CUR1" - I'm getting:
- results for SELECT 1; SELECT2; SELECT 3;
- ReadyForQuery response
- results for SELECT 4;
- one more ReadyForQuery response
from backend
[send_prepared.log & recv_prepared.log]

Is this behavour is correct and expected?

P.s. Tested on Windows with PostgreSQL 8.0, 8.1 and 8.2 with the same
results.

Thank You,
Andrei.
R

Re: PREPARE vs query with MULTIPLE statements

From
Tom Lane
Date:
Andrei Kovalevski <andyk@commandprompt.com> writes:
> 2) If I send it as 'PREPARE "SQL_CUR1" AS SELECT 1; SELECT 2; SELECT3; 
> SELECT4;' and then 'EXECUTE "SQL_CUR1" - I'm getting:
> - results for SELECT 1; SELECT2; SELECT 3;
> - ReadyForQuery response
> - results for SELECT 4;
> - one more ReadyForQuery response
> from backend

> Is this behavour is correct and expected?

You seem to have some odd ideas about what the semicolons mean.
The prepare command there is PREPARE "SQL_CUR1" AS SELECT 1
... no more and no less.
        regards, tom lane

Re: PREPARE vs query with MULTIPLE statements

From
Andrei Kovalevski
Date:
Yes, Thank you!

Tom Lane wrote:
> Andrei Kovalevski <andyk@commandprompt.com> writes:
>   
>> 2) If I send it as 'PREPARE "SQL_CUR1" AS SELECT 1; SELECT 2; SELECT3; 
>> SELECT4;' and then 'EXECUTE "SQL_CUR1" - I'm getting:
>> - results for SELECT 1; SELECT2; SELECT 3;
>> - ReadyForQuery response
>> - results for SELECT 4;
>> - one more ReadyForQuery response
>> from backend
>>     
>
>   
>> Is this behavour is correct and expected?
>>     
>
> You seem to have some odd ideas about what the semicolons mean.
> The prepare command there is PREPARE "SQL_CUR1" AS SELECT 1
> ... no more and no less.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>