Thread: YNT: Re: [SQL] Can the result sets produced in SQL procedures be left open for the use of the calling program?

-------- Orjinal mesaj --------
Kimden: Adrian Klaver <adrian.klaver@aklaver.com>
Tarih: 2 06 2017 12:24 AM (GMT+02:00)
Alıcı: Gugu Rama <gulsumramazanoglu@gmail.com>, pgsql-sql@postgresql.org
Konu: Re: [SQL] Can the result sets produced in SQL procedures be left open for the use of the calling program?

On 06/01/2017 02:15 PM, Gugu Rama wrote:
> Hi,
>
> May i ask, if I write an hll program and make a call to an sql
> procedure, will the result set which is produced in the sql procedure be
> available for the calling program, after the sql prodecure ends?

FYI, Postgres has User Defined Functions(UDF) not stored procedures:

https://www.postgresql.org/message-id/D960CB61B694CF459DCFB4B0128514C26AB7AE%40exadv11.host.magwien.gv.at

Though it does not matter for this question. The answer is yes you can
return a result set that can be consumed by another application. I do it
all the time using Python as the end consumer. For the builtin language
plpgsql see here:

https://www.postgresql.org/docs/9.6/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

>
> Example:
>
> HLL program body (which undertakes frontend tasks, gets input from the user)
> .
> .
> .
> Call to an SQL procedure (all due backend stuff, which creates a result
> set depending on the input from the user)
> .
> .
> .
> HLL program body (sends the data in the result set to the screen)
>
>
> Thank you..
>
> Gulsum


--
Adrian Klaver
adrian.klaver@aklaver.com

On 06/01/2017 03:51 PM, gulsumramazanoglu wrote:
> Adrian thank you for your prompt reply and enlightening info.. though i 
> need to read the sql document thorougly, but for now i just looked at 
> the CREATE FUNCTION command and its compatibility note at the end, and 
> saw that there is an iso standard (and also a cross-platform) 
> compatibility issue.. actually it seems none of the databases provide 
> full compatibility for this command (and for more) i think.. is there 
> any other way of writing sql scripts (say functions, procedures..) which 
> will provide compatibility with iso standards and other db.s? I want to 
> write backend stuff in functions and also stick to the standards..

Short answer:

No

Long answer:

Full cross compatibility is a myth for anything but an extremely simple 
use case and very few applications stay at this stage.  If you want to 
keep logic in the database you will end up writing to that database's 
implementation of the standard and how it creates/use functions. This is 
one of the reasons 3-tier architecture was developed:

https://en.wikipedia.org/wiki/Multitier_architecture#Three-tier_architecture

So a middle layer between the UI and the database where business logic 
resides. Even that falls down when you start making use of database 
specific features. In that case the logic layer grows an adaption layer 
to deal with various databases.

Your best bet is to pick a database you really want to use and build 
against that. Then if you want to use other databases, convert what is 
needed. From the posts that hit the various pgsql* lists that will be 
time consuming.  There are tools that can help, for example moving from 
Oracle and MySQL to Postgres:

http://ora2pg.darold.net/

It still needs a good deal of oversight on the part of the developer.


> 
> Maybe i should ask more specifically: say for instance i want to send 
> some input data to the function and wait for a result set depending on 
> those parameters (inputs).. may i code such a function in a compatible 
> way with iso standards and make it cross-platform as well?
> Maybe a too naive question, but still i want to ask..

See above.

> 
> Thanks again..
> 
> Samsung cihazımdan gönderildi
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com