PostgreSQL call WITH tables inside a function

Question

This is the question:

I want to call a table that i have made inside the WITH from a function.

This is the main query

WITH RECURSIVE session_data AS (
      SELECT * FROM (VALUES(1)) t(ntimes)
) SELECT * FROM my_wonderful_function()

This is the function

CREATE FUNCTION my_wonderful_function()
RETURN TABLE (xtimes int)
AS
$$
DECLARE
BEGIN
      RETURN SELECT * FROM session_data;
END;
$$ LANGUAGE plgsql

I know, this is probably a strange question but i want to do it, anyone knows how to solve this?

Not possible. Move the `WITH` inside the function, or pass individual rows to the function as a parameter. — Bergi, Aug 22 '21 at 17:00
What is your [actual problem](https://meta.stackexchange.com/q/66377)? The code you've shown doesn't actually do anything, and there's no reason for so many `SELECT` subqueries nor for the `RECURIVE`. Yes, it's a strange question. — Bergi, Aug 22 '21 at 17:01
@Bergi this is just piece of code, i know recursive wasn't useful there. — Matteo Sipione, Aug 22 '21 at 17:03
@Bergi I want to make constants parameters that live only during the query execution, so i can call "user_id" inside of any function without always pass it. A lot of client use the same channel to talk with the server, so i need that the constants are "alive" only for the current query. — Matteo Sipione, Aug 22 '21 at 17:05
Please post your actual code, and/or explain the actual problem you need to solve, so that we can suggest an actual solution and not just piece of code that won't be useful. — Bergi, Aug 22 '21 at 17:05
Not sure I understand. Why wouldn't you want to pass in the parameter? Is there a difference between "call" and "query", i.e. do you have multiple calls per query? — Bergi, Aug 22 '21 at 17:07
@Bergi just because now I'm setting some "sessionvars" when I open the connection with the db. I want to migrate it to a multi clients per connection, so I need that's those settings becomes "query-related" and not "connection-related" — Matteo Sipione, Aug 22 '21 at 17:14
You might be looking for [`SET LOCAL`](https://www.postgresql.org/docs/current/sql-set.html), [`current_setting()`](https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SET) and [custom parameters](https://www.postgresql.org/docs/current/runtime-config-custom.html). Notice it only stores strings though. — Bergi, Aug 22 '21 at 17:15
You should use one transaction per "client" (and query), and transaction-scoped session vars instead of connection-scoped ones. The only way to pass parameters into a specific function call in a query is through function arguments. — Bergi, Aug 22 '21 at 17:16
@Bergi oh, I didn't know the existence of that "LOCAL". If I have two different transaction with two different variable for the same parameter ( eg. trans1: language=1 [] trans2: language=2) it will be used the right value in the right transaction? No-one will be overwritten? — Matteo Sipione, Aug 22 '21 at 17:19
Yes, each transaction has its own value. But notice you can't run two transactions on the same connection at the same time anyway, just like you can't run two queries at the same time, so probably even connection-scoped session vars would have worked if you reset them before each query. Using transactions just ensures proper cleanup. — Bergi, Aug 22 '21 at 17:24

score 0 · Answer 1 · answered Aug 23 '21 at 02:49

A CTE (WITH clause) is part of a SELECT (or INSERT/UPDATE/DELETE statement). The scope of the CTE is limited to that statement, so it is impossible to use the CTE outside the statement that defined it.

If you need a “named query” that you can use in several SQL statements, create a view. Views are persistent.

PostgreSQL call WITH tables inside a function

1 Answers1