1

I have a stored procedure and a table. In the table the name of the stored procedure is stored.

CREATE OR REPLACE PROCEDURE "hello" ()
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE INFO 'Hello World!';
END;
$$;

CREATE TABLE "tasks"
(
  "id" integer,
  "name" text
);

INSERT INTO "tasks" ("id", "name") values (1, 'hello');

SELECT "name" from "tasks" where "id" = 1;

Now I want to execute the stored procedure, I have stored in the table.

CALL (SELECT "name" from "tasks" where "id" = 1) ();

How can this be done in Postgresql?

8
  • The search term you're looking for is "dynamic SQL".
    – mustaccio
    Commented Jul 18, 2021 at 12:48
  • @mustaccio When I search for Dynamic SQL I get a description how to embed SQL in C. That is not what I want.
    – ceving
    Commented Jul 18, 2021 at 13:14
  • postgresql.org/docs/current/…
    – user1822
    Commented Jul 18, 2021 at 13:21
  • you can'-call that procedure that way see dbfiddle.uk/…
    – nbk
    Commented Jul 18, 2021 at 14:22
  • @nbk It is possible to write a trampoline procedure: CREATE OR REPLACE PROCEDURE "proc" ("name" text) LANGUAGE plpgsql AS $$ BEGIN EXECUTE format ('CALL %I ()', name); END; $$;. This can be called with a string argument: CALL proc ('hello');. But it is not possible to pass the result of a sub-select to the function: CALL proc ((select string_agg ("name", '') "name" from "tasks" where "id" = 1));.
    – ceving
    Commented Jul 18, 2021 at 14:26

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.