CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Zhiyong Cai
Zhiyong Cai

Reputation: 123

How to pass a with query as a parameter to a PL/pgSQL function?

F(aTable TEXT) is a PL/pgSQL function, which use a table as parameter. When I call this function like this:

with w as ( ... ) select * from F('w')

It return an error: w not exists.

Upvotes: 0

Views: 190

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657617

w in your query is the name of a derived table (the "common table" produced from the CTE). This is a special kind of temporary table visible exclusively in the DML command the CTE is attached to - unlike regular tables (visible globally) or temporary tables (visible inside the same session), there are no entries in the system catalogs for w. You can also not create indexes for it or modify it in any way.

The function F takes a text parameter. You kept the function definition a secret, but it probably runs dynamic SQL with EXECUTE inside. w is not visible inside EXECUTE, so there is no way to do this.

Create a temporary table instead.

Or better yet, integrate the whole query, including code from the function. This is typically fastest.

Upvotes: 1

Related Questions