2

I have created a plpythonu function that should return a table with multiple columns. At present, it returns a single column with multiple components.

I came across this answer which is A) a little old and B) requires me to separate the components into a table outside of the function.

Here's a simple function that illustrates the problem:

CREATE TYPE foo AS (a INT, b INT);
CREATE OR REPLACE FUNCTION bar () RETURNS SETOF foo AS $$
    return [(1,2),(3,4)]
$$ LANGUAGE plpythonu;

SELECT bar();

This returns two records but only a single column. I want it to return a table with two records and two columns. Is this not possible? That would seem odd but an hour's worth of Googling has gotten me nowhere.

  • What about return [((1,2),(3,4)] ( a one-element list, containing a tuple with inner tuples) ? – Craig Ringer Aug 27 '15 at 4:04
  • Nope. Still doesn't work. You have a typo, but I assume you intended it to be [((1,2)),((3,4))]. Trying it as [((1,2),(3,4))] returns a syntax error. – spencerrecneps Aug 27 '15 at 10:53
  • No, I meant [((1,2),(3,4))]. You're right that I had a typo, I missed a closing paren. It should be a 1-element list, containing a 2-element tuple, each element of which is a 2-element tuple. If plpythonu supports representing composite types as tuples, that should work. I haven't tested. – Craig Ringer Aug 27 '15 at 10:54
2

The simple way to do that would be to call your bar() function like this:

postgres@[local]:5432:postgres:=# SELECT * FROM bar();
 a | b
---+---
 1 | 2
 3 | 4
(2 rows)

Time: 0.622 ms
postgres@[local]:5432:postgres:=#

Which returns as a record type in this context, with two rows and two columns.

Hope that helps. =)

  • Perhaps my code runs differently on your machine, but that's not the output that I get.from my code snippet. I see two rows but the two values are combined into a single column as a tuple. – spencerrecneps Sep 1 '15 at 19:35
  • Oh. Interesting. I got those results on PostgreSQL 9.4 by copy-pasting your code and doing SELECT * FROM bar() above instead of the SELECT bar(). Which leads me to ask the question I should have asked beforehand, what version of PostgreSQL are you using? – Kassandry Sep 1 '15 at 22:02
  • Huh. i never thought to try SELECT * FROM bar() instead of SELECT bar(). Rookie mistake, I guess. Thanks for pointing it out. – spencerrecneps Sep 2 '15 at 1:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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