CODEWITHSUNDEEP
sqlpostgresqlsql-function
user20137453
user20137453

Reputation:

POSTGRESQL How to return multiple rows from SQL function?

I'm new to databases and SQL and i've been messing around with functions on POSTGRESQL and I made a simple function to select all names from a table.

But the function, when called, returns just a single row as opposed to all rows being returned when i use SELECT c_name FROM customers; instead of the code shown below:

CREATE OR REPLACE FUNCTION get_cust_names()
RETURNS varchar AS
$body$
SELECT c_name FROM customers;
$body$
LANGUAGE SQL

Function call

SELECT get_cust_names()

this returns just a SINGLE row and this isnt code that i will use in a project etc. I'm just curious as to why postgresql behaves this way.

Upvotes: 0

Views: 129

Answers (1)

user330315
user330315

Reputation:

You need to declare the function as returns table(..) or returns setof

CREATE OR REPLACE FUNCTION get_cust_names()
RETURNS table(cust_name varchar) 
AS
$body$
  SELECT c_name 
  FROM customers;
$body$
LANGUAGE SQL;

Then use it like a table:

select *
from get_cust_names();

Upvotes: 0

Related Questions