Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

postgres "Returns Table" returns a string

Tags:

postgresql

I am using the SQL language on Postgres to return a table using the RETURNS TABLE command:

CREATE OR REPLACE FUNCTION procreadbudget()
  RETURNS TABLE(budgetmonth character, budgetincome numeric, budgetexpense numeric) AS
$BODY$
SELECT budget_month, budget_income, budget_expense FROM budget ORDER BY unique_id;
$BODY$
  LANGUAGE 'sql' VOLATILE

All the data is returned as a comma delimited string, not as a table:

"(Jan,1123,1201)"
"(Feb,1098,996)"
"(Mar,1545,1345)"
"(Apr,1564,1952)"
"(May,1123,990)"
"(Jun,1345,1234)"
"(Jul,1234,878)"
"(Aug,1139,1187)"
"(Sep,1076,1123)"
"(Oct,873,956)"
"(Nov,1298,1423)"
"(Dec,1123,1324)"

Any suggestions please, Mike

like image 323
Michael Avatar asked Jul 23 '10 08:07

Michael


People also ask

How do I return a table in PostgreSQL?

To return a table from the function, you use RETURNS TABLE syntax and specify the columns of the table. Each column is separated by a comma (, ). In the function, we return a query that is a result of a SELECT statement.

What is $$ in Postgres?

A dollar sign ($) followed by digits is used to represent a positional parameter in the body of a function definition or a prepared statement. In other contexts the dollar sign may be part of an identifier or a dollar-quoted string constant.

What is return query in Postgres?

RETURN QUERY appends the results of executing a query to the function's result set. RETURN NEXT and RETURN QUERY can be freely intermixed in a single set-returning function, in which case their results will be concatenated.

What is text [] in Postgres?

PostgreSQL supports a character data type called TEXT. This data type is used to store character of unlimited length. It is represented as text in PostgreSQL. The performance of the varchar (without n) and text are the same. Syntax: variable_name TEXT.


1 Answers

Use

SELECT * FROM procreadbudget();

instead of

SELECT procreadbudget();
like image 175
Frank Heikens Avatar answered Sep 24 '22 19:09

Frank Heikens