Return Table in StoredProceure/Function

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Return Table in StoredProceure/Function

İlyas Derse


How can I return table in Stored Procedure ? I can do it in function but I have inout parameters.So I can not create in function. What can I do this case ?

I guess,It should be like for function :

CREATE or REPLACE FUNCTION public."test" (INOUT "x" integer, INOUT "y" text)

RETURNS TABLE  (
"id" integer,
"filesize" character varying(36)
)
AS $$
 BEGIN
RETURN QUERY
SELECT * FROM    public."tbl_employees" ;

END;
$$ LANGUAGE plpgsql;

Thanks.

Reply | Threaded
Open this post in threaded view
|

Re: Return Table in StoredProceure/Function

Pavel Stehule
Hi

st 20. 11. 2019 v 16:01 odesílatel İlyas Derse <[hidden email]> napsal:


How can I return table in Stored Procedure ? I can do it in function but I have inout parameters.So I can not create in function. What can I do this case ?

I guess,It should be like for function :

CREATE or REPLACE FUNCTION public."test" (INOUT "x" integer, INOUT "y" text)

RETURNS TABLE  (
"id" integer,
"filesize" character varying(36)
)
AS $$
 BEGIN
RETURN QUERY
SELECT * FROM    public."tbl_employees" ;

END;
$$ LANGUAGE plpgsql;

Thanks.


You can returns cursor via OUT variable. That's all. There is not any other possibility. Procedures in Postgres can returns results only via OUT variables.

Regards

Pavel
Reply | Threaded
Open this post in threaded view
|

Re: Return Table in StoredProceure/Function

Thomas Kellerer
In reply to this post by İlyas Derse
İlyas Derse schrieb am 20.11.2019 um 09:18:

> How can I return table in Stored Procedure ? I can do it in function but I
> have inout parameters.So I can not create in function. What can I do this
> case ?
>
> I guess,It should be like for function :
>
> CREATE or REPLACE FUNCTION public."test" (INOUT "x" integer, INOUT "y" text)
>
> RETURNS TABLE  ("id" integer,"filesize" character varying(36))AS $$
>  BEGINRETURN QUERYSELECT * FROM    public."tbl_employees" ;
> END;$$ LANGUAGE plpgsql;
>

Procedures aren't meant to return anything (in Postgres specifically and in Computer Science in general).

If you want to return something use a function.




Reply | Threaded
Open this post in threaded view
|

Re: Return Table in StoredProceure/Function

Tony Shelver
Well then SQL Server breaks that rule big time :)
Most people coming from a SQL Server background expect procedures to return a result set that can be queried, and in-out or out parameters to return variables for further information.



On Wed, 20 Nov 2019 at 17:20, Thomas Kellerer <[hidden email]> wrote:
İlyas Derse schrieb am 20.11.2019 um 09:18:

> How can I return table in Stored Procedure ? I can do it in function but I
> have inout parameters.So I can not create in function. What can I do this
> case ?
>
> I guess,It should be like for function :
>
> CREATE or REPLACE FUNCTION public."test" (INOUT "x" integer, INOUT "y" text)
>
> RETURNS TABLE  ("id" integer,"filesize" character varying(36))AS $$
>  BEGINRETURN QUERYSELECT * FROM    public."tbl_employees" ;
> END;$$ LANGUAGE plpgsql;
>

Procedures aren't meant to return anything (in Postgres specifically and in Computer Science in general).

If you want to return something use a function.




Reply | Threaded
Open this post in threaded view
|

Re: Return Table in StoredProceure/Function

Pavel Stehule


čt 21. 11. 2019 v 7:34 odesílatel Tony Shelver <[hidden email]> napsal:
Well then SQL Server breaks that rule big time :)
Most people coming from a SQL Server background expect procedures to return a result set that can be queried, and in-out or out parameters to return variables for further information.

yes, SQL server is absolutely unique in this case. Oracle, DB2, and Postgres has different design

If you have SQL Server background, and you would to write stored procedures, start with doc, please https://www.postgresql.org/docs/current/plpgsql.html

lot of things are really different. Postgres SQL, stored procedures are close to Oracle, and very far to T-SQL

Regards

Pavel





On Wed, 20 Nov 2019 at 17:20, Thomas Kellerer <[hidden email]> wrote:
İlyas Derse schrieb am 20.11.2019 um 09:18:

> How can I return table in Stored Procedure ? I can do it in function but I
> have inout parameters.So I can not create in function. What can I do this
> case ?
>
> I guess,It should be like for function :
>
> CREATE or REPLACE FUNCTION public."test" (INOUT "x" integer, INOUT "y" text)
>
> RETURNS TABLE  ("id" integer,"filesize" character varying(36))AS $$
>  BEGINRETURN QUERYSELECT * FROM    public."tbl_employees" ;
> END;$$ LANGUAGE plpgsql;
>

Procedures aren't meant to return anything (in Postgres specifically and in Computer Science in general).

If you want to return something use a function.




Reply | Threaded
Open this post in threaded view
|

Re: Return Table in StoredProceure/Function

Thomas Kellerer
In reply to this post by Tony Shelver
Tony Shelver schrieb am 21.11.2019 um 07:33:
> Well then SQL Server breaks that rule big time :)

I am aware of that - but at the end it's essentially the only DBMS (except for Sybase because of their common roots) that works that way.

A migration from SQL Server to Oracle (or MySQL or DB2 or Firebird) would have the same problems.

> Most people coming from a SQL Server background expect procedures to
> return a result set that can be queried, and in-out or out parameters
> to return variables for further information.

One very important aspect of a migration is to also migrate your mindset and the way you solve problems (especially when migrating between two products that behave so differently).
The best practices for System A are not always the best practices for System B

Insisting on "But this is the way we did it in System A" is a pretty sure recipe for a failing migration.

Note that this is true in both directions: if you apply best practices from Postgres or Oracle when migrating _to_ SQL Server you are in for very nasty surprises as well.

Thomas