CODEWITHSUNDEEP
postgresql
Ir S
Ir S

Reputation: 495

postgresql return single row from function after select statement

How can I return single row from postgresql function? What type should I use after returns? These is my code:

create or replace function get_perf()
returns ???? as $$  
select task_ext_perf.performance, task_ext_perf.unit from task_ext_perf order by task_ext_perf.tm limit 1; 
$$
language sql;

Upvotes: 6

Views: 16939

Answers (1)

Blackus
Blackus

Reputation: 7203

Considering a table User like this :

User
------
id
name

You can return a single row of a table with a procedure like this:

CREATE OR REPLACE function get_user()
RETURNS User AS $$  
  SELECT id, name
  FROM User
  WHERE id = 1
$$
language sql;

If you want to return a more elaborated row, you have to return a record, with OUT parameters.

CREATE FUNCTION get_user(OUT id integer, OUT name character varying, OUT linkedString character varying)
RETURNS record LANGUAGE sql AS $$
 SELECT
   u.id, u.name, ot.string
 FROM Users u
 INNER JOIN OtherTable ot ON ot.user_id = u.id
 WHERE id = 1
$$;

Upvotes: 5

Related Questions