3

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.

3
  • What about return [((1,2),(3,4)] ( a one-element list, containing a tuple with inner tuples) ? Commented Aug 27, 2015 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. Commented Aug 27, 2015 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. Commented Aug 27, 2015 at 10:54

1 Answer 1

3

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. =)

3
  • 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. Commented Sep 1, 2015 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
    Commented Sep 1, 2015 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. Commented Sep 2, 2015 at 1:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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