473,287 Members | 1,663 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Postgre Function returning output

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
2 2313
Luuk
1,047 Expert 1GB
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
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

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

Similar topics

17
by: Roland Hall | last post by:
Is there a way to return multiple values from a function without using an array? Would a dictionary object work better? -- Roland Hall /* This information is distributed in the hope that it...
3
by: Jochen Zeischka | last post by:
I'm puzzled. When compiling this: template<class ValRes, class Val1, class Val2> Veld<ValRes>& mult(Veld<ValRes>& res, const Veld<Val1>& v1, const Veld<Val2>& v2) { // something return res; }...
3
by: Najib Abi Fadel | last post by:
Is there any postgres function that do the following: select func(9123456789); 9 123 456 789 the function is given a number and it returns a "readable" string representation of the number...
5
by: shyam | last post by:
Hi All I have to write a function which basically takes in a string and returns an unknown number( at compile time) of strings i hav the following syntax in mind char *tokenize(char *) ...
14
by: Protoman | last post by:
How would you write a function returning a function pointer and why would you need to do this? Is it: int(*)(int&) fn(int& arg); Thanks!!!
2
by: Tany | last post by:
How can I declare function returning array of Integer pointers . Please help !!
17
by: I.M. !Knuth | last post by:
Hi. I'm more-or-less a C newbie. I thought I had pointers under control until I started goofing around with this: ...
14
by: Fabian Steiner | last post by:
Hello! I have got a Python "Device" Object which has got a attribute (list) called children which my contain several other "Device" objects. I implemented it this way in order to achieve a kind...
3
by: John Turner | last post by:
typedef void (*vfp)(); typedef vfp (*fp)(); static fp hello() { printf("Hello.\n"); return (fp)&hello; } main(){
1
by: eros | last post by:
CREATE OR REPLACE FUNCTION f_customerlogininfo(varchar, varchar, varchar) RETURNS public.v_customerlogininfo AS ' DECLARE p_schm ALIAS FOR $1; p_contact ALIAS FOR $2; p_status ALIAS...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.