Home Postgres EXECUTE with CTE
Reply: 0

Postgres EXECUTE with CTE

user1791
1#
user1791 Published in July 23, 2018, 9:50 am

Is it possible to EXECUTE a prepared statement using parameters you'd get from a CTE ?

The below samples are simplified versions of my code, but this is replicating exactly the problem I have.

Here's how far I've been able to go - without a CTE :

BEGIN;

CREATE TEMPORARY TABLE testTable
(
    col1 NUMERIC,
    col2 TEXT
) ON COMMIT DROP;

INSERT INTO testTable
VALUES (1, 'foo'), (2, 'bar');

PREPARE myStatement AS
    WITH cteTable AS
    (
        SELECT col1, col2
        FROM testTable
        WHERE col1 = $1
    )
    SELECT col2 FROM cteTable;


EXECUTE myStatement(2);

DEALLOCATE myStatement;
COMMIT;

Here's the result:

col2


bar

And now, here's what I am trying to achieve :

BEGIN;

CREATE TEMPORARY TABLE testTable
(
    col1 NUMERIC,
    col2 TEXT
) ON COMMIT DROP;

INSERT INTO testTable
VALUES (1, 'foo'), (2, 'bar');

PREPARE myStatement AS
    WITH cteTable AS
    (
        SELECT col1, col2
        FROM testTable
        WHERE col1 = $1
    )
    SELECT col2 FROM cteTable;

-- Using a CTE here to get the parameters for the prepared statement
WITH parameters AS
(
    SELECT 2 val
)
EXECUTE myStatement(SELECT val FROM parameters);

DEALLOCATE myStatement;
COMMIT;

The error message I'm having is

Syntax error at or near EXECUTE

Even if try to run the EXECUTE part without attempting to use the CTE values, I still have the same error message.

As I haven't been able to find anyone else having the same issue despite my researches, I guess I could be doing it wrong.. If so could someone please point me into the right direction ?

Thanks

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.310002 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO