Creating functions in postgresql

Question

I am new to SQL, so please try not to be overly critical about my question, I need to create a function which would return me a table (say for example "machine") , which would have a column called "aggtablename" and the rows would be filled with values derived from a database. Here is what i tried and the following error came....so please help me in making my syntax correct, THANKS..

CREATE FUNCTION aggtable() RETURNS TABLE (machineid, serveraggtablename)
AS $table$  
BEGIN     
  RETURN QUERY    
  SELECT m.machineid, m.serveraggtablename   
  FROM machine m 
   END; 
 $table$  
LANGUAGE plpgsql;  

select aggtable();

ERROR: function aggtable() does not exist LINE 1: select aggtable(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Community · Accepted Answer · 2017-05-23 10:32:24Z

0

The arguments of the table you're returning do not have any type.

try adding a type such as machineid int.

check this post

How can a Postgres Stored Function return a table

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered May 29, 2014 at 16:50

Miguel Rosales

8094 gold badges15 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mr. Alien Over a year ago

Link only answers are not encouraged, please post some code and explain

FuzzyTree · Accepted Answer · 2014-05-29 16:57:23Z

0

Try this

CREATE TYPE machineType as (machineid int, serveraggtable character varying);

CREATE FUNCTION aggtable() RETURNS SETOF machineType AS 
'SELECT m.machineid, m.serveraggtablename FROM machine m;' 
LANGUAGE 'sql';

SELECT * FROM aggtable();

https://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions

edited May 29, 2014 at 16:57

answered May 29, 2014 at 16:40

FuzzyTree

32.4k3 gold badges58 silver badges87 bronze badges

2 Comments

Son_Goku Over a year ago

Thanks for your help but, now there is one more issue, i changed the syntax...now the error i am getting is : CREATE TYPE machineType as (machineid int, serveraggtable character varying); CREATE FUNCTION aggtable() RETURNS SETOF machineType AS $$ BEGIN SELECT id, serveraggtablename FROM machine ; END; $$ LANGUAGE 'plpgsql'; SELECT * FROM aggtable(); ERROR: query has no destination for result data

Clodoaldo Neto Over a year ago

@user3688338 That is another question. If this answer solved the posted question then accept it.

Collectives™ on Stack Overflow

Creating functions in postgresql

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related