2

I'm sorry if this issue is so simple. I have some query that can run normally in MySQL Stored Procedure. But now I'm migrating it to PostgreSQL, that query is result of Concat from another query. The query simply like this:

DECLARE

A TEXT;

BEGIN

A = concate(sql);  /*in MySQL I just use SET A = CONCATE(sql)*/

PREPARE stmt AS A; /* in MySQL normally run with PREPARE stmt FROM A;*/
EXECUTE stmt;

END

But I always get an error :

ERROR:  syntax error at or near "A"
LINE 85:  PREPARE stmt AS A;

My questions are :

  1. How can I access the variable A so it can run normally?
  2. With or without PREPARE, is there any different of result when we run the query? If use PREPARE what is the advantages?

Thanks expert!

Ugy Astro
  • 357
  • 3
  • 6
  • 16
  • You should create a function . Refer https://stackoverflow.com/questions/11948131/postgresql-writing-dynamic-sql-in-stored-procedure-that-returns-a-result-set – Kaushik Nayak Mar 13 '18 at 07:04

1 Answers1

1

You can create and use a prepared statement in PL/pgSQL, but bear in mind that PL/pgSQL's EXECUTE is different from the SQL statement EXECUTE.

So if you want to execute a prepared statement in PL/pgSQL, you will have to use dynamic SQL to avoid the confusion between PL/pgSQL's EXECUTE and the SQL statement of the same name.

DO $$BEGIN PREPARE x AS SELECT 42; END;$$;

EXECUTE x;

 ?column? 
----------
       42
(1 row)

DO
$$DECLARE a integer;
BEGIN
   EXECUTE 'EXECUTE x' INTO a;
   RAISE NOTICE 'a = %', a;
END;$$;

NOTICE:  a = 42
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks sir, your explain so helpful. The query running well like your example. When I search I didn't find like your explain. So the main different between MySQL and PostgreSQL when using `PREPARE` is with adding `EXECUTE` before `PREPARE label` and `'` (single quote) at `'PREPARE label'`. right? – Ugy Astro Mar 14 '18 at 04:04
  • I realize that my answer was confusing. Actually, you don't need dynamic SQL (PL/pgSQL's `EXECUTE`) to run `PREPARE`, but you need it to run the SQL command `EXECUTE` in order to avoid confusion between these two kinds of `EXECUTE`. See my revised answer. – Laurenz Albe Mar 14 '18 at 06:19