postgresql constrain view with where and send result to function?

Question

I need to do some very complex manipulation on a set of rows from a very large table.

It seems like the manipulation ("backfill" null attributes based on a JOIN, UNION & several ORDER BYs) is best accomplished in a function. To minimize the # of rows I need a WHERE clause at the outset.

And because all of this will talk to Rails the outer layer needs to be a VIEW.

Minus the backfill what I have is

CREATE OR REPLACE VIEW testing.for_sale_layouts_view
AS SELECT * from bestforsalelayout((select address_id from public.for_sale_layouts limit 1));
;

and

CREATE OR REPLACE FUNCTION bestforsalelayout(addr_id int)
RETURNS public.for_sale_layouts
AS $$
    select * from public.for_sale_layouts where address_id = addr_id;
$$
LANGUAGE SQL IMMUTABLE;

Called like

select * from testing.for_sale_layouts_view where address_id = <addr_id>;

This sort of works, however if there is more than one row returned by for_sale_layouts the function returns nothing, a singleton row works fine.

I'm at a loss, pointers in a working direction would be greatly appreciated.

Gordon Linoff · Accepted Answer · 2013-06-04 02:03:10Z

1

If you want to return more than one row, then you need the return table form of the function.

Check out the documentation here.

CREATE OR REPLACE FUNCTION bestforsalelayout(addr_id int)
RETURNS table(for_sale_layouts int)
AS $$
    select for_sale_layouts from public.for_sale_layouts where address_id = addr_id;
$$

answered Jun 4, 2013 at 2:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Mike Summers Over a year ago

This function will only ever return one row. The problem seems to be if the subselect in the view contains more than one row, the point there is just to get the address_id to pass into the function.

Collectives™ on Stack Overflow

postgresql constrain view with where and send result to function?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related