Creating a function in Postgresql that does not return composite values

Question

I'm learning how to write functions in Postgresql. I've defined a function called _tmp_myfunction() which takes in an id and returns a table (I also define a table object type called _tmp_mytable)

-- create object type to be returned
CREATE TYPE _tmp_mytable AS (
    id      integer, 
    cost    double precision
    );

-- create function which returns query
CREATE OR REPLACE FUNCTION _tmp_myfunction(
    id    integer
    )
RETURNS SETOF _tmp_mytable AS $$
BEGIN  
  RETURN QUERY 
  SELECT
    sales.gid,
    cost  
  FROM 
    sales
  WHERE
    id = sales.gid;
  END;
$$ LANGUAGE plpgsql;

This works fine when I use one id and call it using the following approach:

SELECT * FROM _tmp_myfunction(402);

enter image description here

What I would like to be able to do is to call it, but to use a column of values instead of just one value. However, if I use the following approach I end up with all values of the table in one column, separated by commas:

-- call function using all values in a column
SELECT _tmp_myfunction(t.id)
FROM transactions as t;

enter image description here

I understand that I can get the same result if I use SELECT _tmp_myfunction(402); instead of SELECT * FROM _tmp_myfunction(402); but I don't know how to construct my function in such a way that I do not get composite values when I pass in a column of values.

Just updated my variable names to make this a little clearer (I had changed them to make my question clearer and missed that there would be a conflict). — djq
– djq, Commented Dec 10, 2012 at 23:49
@ErwinBrandstetter While you can call a set-returning function in SELECT, the behaviour can be truly bizarre. It's a legacy PostgreSQL specific hack that should be abandoned as soon as PostgreSQL 9.3's LATERAL is available for use. For just how weird, compare SELECT generate_series(1,3), generate_series(1,3); to SELECT generate_series(1,3), generate_series(1,4); . The 1st returns three rows, pairs of results in order. The second returns twelve rows, all possible pairings of results, like a cross product. — Craig Ringer
– Craig Ringer, Commented Dec 10, 2012 at 23:58

Ihor Romanchenko · Accepted Answer · 2012-12-10 23:23:04Z

2

You can write something like that:

SELECT (t2.function_row).id,
       (t2.function_row).cost
FROM (SELECT _tmp_myfunction(t.id) as function_row
     FROM transactions  t ) t2;

It will give you the fields, instead of composite rows.

answered Dec 10, 2012 at 23:23

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Craig Ringer Over a year ago

Note that PostgreSQL may execute the function repeatedly for each row, once per (func_row).column. Add a RAISE NOTICE to the function to see what I mean. 9.3's LATERAL support should finally fix this. There's a depesz article with detail and examples, I just can't seem to find it at the moment.

Erwin Brandstetter Over a year ago

@Craig: You probably mean this one. Looking forward to the feature, too.

Collectives™ on Stack Overflow

Creating a function in Postgresql that does not return composite values

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related