CODEWITHSUNDEEP
postgresqlcommon-table-expression
Neel Patel
Neel Patel

Reputation: 315

Common Table Expression in PostgreSQL

I am just learning the CTE and I want to create the dynamic query inside the WITH clause.

Below is what i have written code.

WITH name1 AS (
    SELECT schema_name as my_schema
    FROM public.schema_table
), name2 AS (
    SELECT num_rows as my_row
    FROM my_schema.row_table
)
SELECT my_row
from name2;

From the first query inside the WITH give number of schema exist in one database and that schema name return by

SELECT schema_name as my_schema 
FROM public.schema_table

I want to use in second query as I am saving it to my_schema.

but when i run this query then it gives me error like my_schema not exists that correct because I want to use the value my_schema contains.

How can you do that?

Upvotes: 0

Views: 786

Answers (1)

Chris Farmiloe
Chris Farmiloe

Reputation: 14175

Firstly, you do not appear to have used name1 anywhere in your query, so I suspect you may not have understood WITH fully.

However it looks like you might have multiple tables called row_table each living in it's own schema and want to create a query that will let you choose which schema to fetch from.

FROM my_schema.row_table
     ^^^^^^^^^

my_schema is not a variable, it is the name of the schema. To do what you want you are going to have to use the pg_catalog tables to find the oid for the relation within the schema that you need and look up the information that way.

An alternative solution could be to manipulate the search path to do your bidding:

BEGIN;
    SET LOCAL SCHEMA 'my_schema';
    SELECT num_rows FROM row_table;   -- will use my_schema.
COMMIT;

Upvotes: 3

Related Questions