By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,337 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

Postgre Function returning output

P: 1
Hello,

I am new to PostgreSQL and i would like to achieve the same result as a stored procedure in MSSQL returns.

In SQL i can return the output of the query with out defining any return type.

Eg: Create procedure mysp (table_name varchar(25)
as
begin

if table_name = 'Color'
select * from color;
end

if table_name='company'
select * from company;

End

Here there may be any number of columns in color and company tables.

i would like to achieve the same in postgre SQL by using functions. I believe Postgres do now support creating stored procedure as SQL.

Thanks in Advance
Dec 13 '18 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 1,035
When saying "In SQL i can return the output of the query with out defining any return type."

The docs say: "There are three ways of returning data from a procedure to a calling program: result sets, output parameters, and return codes. This topic provides information on the three approaches. "

May I draw the conclusion that a return type IS defined (explicitly by MS-SQL)?

Because you example is 'bad-database-design', this is not possible in PostgreSQL, see function-returns-table
Here is is needed to specify which fields are returned.


P.S. Yes I will not say why I think above is 'bad-database-design'!
Dec 14 '18 #2

P: 3
CREATE OR REPLACE FUNCTION get_table(g_table_name character varying)
RETURNS SETOF record
LANGUAGE plpgsql
AS $function$
Declare
l_sonuc RECORD;
BEGIN

For l_sonuc In
EXECUTE 'Select * from '||g_table_name||' limit 10'
Loop

RETURN NEXT l_sonuc;

End loop;

END;
$function$
-----------------------------------------------------------
SELECT * FROM get_table('tb_customer') as (id Integer, name Varchar, las_name varchar);

**** remember to write the name and type of the returned values
Dec 26 '18 #3

Post your reply

Sign in to post your reply or Sign up for a free account.