creating a view in a postgresql function

Question

I have a function that proccess some data:

CREATE OR REPLACE FUNCTION A(x integer, y timestamp with time zone, z integer[])
      RETURNS SETOF typea AS

$BODY$
SELECT *
FROM func1($1,$2,$3) as a
WHERE ...


$BODY$
LANGUAGE sql VOLATILE

How can I convert the query:

SELECT *
FROM func1($1,$2,$3) as a
WHERE ...

into a VIEW ? The thing is that func1 needs the function A parameters...

What I have now is this:

CREATE OR REPLACE FUNCTION A(x integer, y timestamp with time zone, z integer[])
      RETURNS SETOF typea AS
$BODY$
CREATE OR REPLACE VIEW   myView as (select * from  func1($1,$2,$3));
select * from myView ;
$BODY$
LANGUAGE sql VOLATILE

and i get

ERROR: relation "myView " does not exist

in the select * from myView ;

Gordon Linoff · Accepted Answer · 2015-07-26 12:23:16Z

1

I think the problem is that Postgres functions are handled in two steps. In the first step, the function is compiled. In the second, it is executed.

If the view does not exist, then it is not created during the compilation stage. However, the select will fail, because the view doesn't exist.

Creating a view in a function seems strange to me. However, I can think of two work-arounds:

Be sure the view exists before the function is created.
Use dynamic SQL to access the view within the function.

answered Jul 26, 2015 at 12:23

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

3 Comments

avi Over a year ago

This function generate some data, work on it then gives final result. What I want is to view the result before and after the function did it's work. The first step of the function is to generate the data so the second step needs to be saving it. and that is where i'm stuck. How would U suggest to do that?

Gordon Linoff Over a year ago

I would suggest that you either save the results into a table or print them out. A view doesn't seem like the right solution to this problem. You might want to ask another question describing what you are really trying to accomplish, along with sample data and desired results.

avi Over a year ago

You said Dynamic sql can solve it. How would u do that?

Collectives™ on Stack Overflow

creating a view in a postgresql function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related