current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite
1

I have a Postgres function which is returning a table:

CREATE OR REPLACE FUNCTION testFunction() RETURNS TABLE(a int, b int) AS
$BODY$
DECLARE a int DEFAULT 0;
DECLARE b int DEFAULT 0;
BEGIN
CREATE TABLE tempTable AS SELECT a, b;
RETURN QUERY SELECT * FROM tempTable; 
DROP TABLE tempTable;
END;
$BODY$
LANGUAGE plpgsql;

This function is not returning data in row and column form. Instead it returns data as:

(0,0)

That is causing a problem in Coldfusion cfquery block in extracting data. How do I get data in rows and columns when a table is returned from this function? In other words: Why does the PL/pgSQL function not return data as columns?

share|improve this question
    
Why are you using the temp table? That is totally unnecessary and makes things slower without having any advantages (and you could use a simple SQL function which would make things a bit faster) –  a_horse_with_no_name Feb 2 '13 at 5:56
add comment

1 Answer

up vote 5 down vote accepted

To get individual columns instead of the composite type, call the function with:

SELECT * FROM testFunction();

Just like you would select all columns from a table.
Also consider this reviewed form of your test function:

CREATE OR REPLACE FUNCTION testfunction()
  RETURNS TABLE(a int, b int) AS
$func$
DECLARE
   _a int := 0;
   _b int := 0;
BEGIN
   CREATE TEMP TABLE tbl AS SELECT _a, _b;
   RETURN QUERY SELECT * FROM tbl;
   DROP TEMP TABLE tempTable;
END
$func$ LANGUAGE plpgsql;

In particular:

  • DECLARE key word is only needed once.
  • Avoid declaring parameters that are already (implicitly) declared as OUT parameters in the RETURNS TABLE (...) clause.
share|improve this answer
    
Its correct and thanks for answer. –  Satish Sharma Feb 4 '13 at 7:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.