PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Execute dynamic inserts in aprocedure

I’m new to Postgres and trying to create code that inserts data from several tables into a single table.

All source tables have a name starting with "B3_HIST_", differing only in terms of year (they are named from "B3_HIST_2015" to "B3_HIST_2023").

I use 3 variables, concatenate names and use them in a loop like this:

create or replace procedure dbo.SP_B3_HIST ()
language plpgsql
AS $$
declare
    nametab TEXT := 'dbo.B3_HIST_';
    startyr INTEGER := 2015;
    lastyr INTEGER := 2023;
begin
    while startyr <= lastyr  loop

        INSERT INTO TEMP1 SELECT * FROM (nametab || startyr )
        startyr = startyr  + 1
    END LOOP;
END; $$

The problem:

ERROR: string between dollars did not end in or near "$$
declare
    nametab TEXT := 'dbo.B3_HIST_'"
LINE 3: AS$$
^

How to do this properly?

Tags:

2

Answers


  1. 0

    You can loop to run a separate INSERT per source table. (Like for big tables and COMMIT after each table?) Needs correct syntax, of course, like Islingre pointed out in a comment …

    Typically, building a single statement (with plain SQL) and executing is simpler and faster:

    CREATE OR REPLACE PROCEDURE dbo.sp_b3_hist()
  LANGUAGE plpgsql AS
$proc$
DECLARE
   nametab text := 'dbo.b3_hist_';  -- lower case
   startyr int := 2015;
   lastyr  int := 2023;
BEGIN
-- RAISE NOTICE '%', (  -- debug first?
   EXECUTE (
   SELECT E'INSERT INTO temp1 n'
       || string_agg('TABLE ' || nametab || g, E'nUNION ALL ')
   FROM   generate_series(startyr, lastyr) g
   );
END
$proc$;

CALL dbo.sp_b3_hist();

    Related:

    If it’s a one-time operation, use a DO command instead of persisting a procedure.

    Or use gexec in psql.

    See:

    Avoid mixed-case identifier in Postgres to make your life easier.

    Login or Signup to reply.
  2. 0

    I just follow your solution. Make a minor change in the stored procedures INSERT statement.
    The problem was regarding dynamic name of tables in stored procedure. There are different ways to solve it. I use quote_ident() function to resolve the issue. As per documentation of postgresql it is:

    quote_ident ( text ) → text

    Returns the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary (i.e., if the string contains non-identifier characters or would be case-folded). Embedded quotes are properly doubled
    quote_ident(‘Foo bar’) → "Foo bar"

    Let’s start the actual work.

    1. Creating tables(3) and appending test data
    CREATE TABLE IF NOT EXISTS public.B3_HIST_2015
(
    TranDate Date,
    amount numeric(12,2)
);
INSERT INTO public.B3_HIST_2015(
    TranDate, Amount)
    VALUES ('01-Jan-2015', 2000), ('01-Feb-2015', 3000), ('01-Mar-2015', 4000);
    
CREATE TABLE IF NOT EXISTS public.B3_HIST_2016
(
    TranDate Date,
    amount numeric(12,2)
);
INSERT INTO public.B3_HIST_2016(
    TranDate, Amount)
    VALUES ('02-Apr-2016', 2000), ('03-May-2016', 3000), ('04-Jun-2016', 4000);

CREATE TABLE IF NOT EXISTS public.B3_HIST_2017
(
    TranDate Date,
    amount numeric(12,2)
);
INSERT INTO public.B3_HIST_2017(
    TranDate, Amount)
    VALUES ('12-Jul-2017', 2500), ('13-Aug-2017', 3007), ('14-Sep-2017', 4060);
    
CREATE TABLE IF NOT EXISTS public.TEMP1
(
    TranDate Date,
    amount numeric(12,2)
);
    1. Creating Stored Procedure
    create or replace procedure public.SP_B3_HIST ()
language plpgsql
AS $$
declare
    nametab TEXT := 'b3_hist_';
    temptab TEXT :='';
    startyr INTEGER := 2015;
    lastyr INTEGER := 2017;
begin

    while startyr <= lastyr  loop
        temptab = CONCAT(nametab, startyr);
        execute 'INSERT INTO TEMP1(TranDate, Amount) (SELECT TranDate, Amount FROM ' || quote_ident(temptab) || ')';
        startyr = startyr  + 1;
    END LOOP;

END; $$;
    1. Finally Calling the procedure for execution
    call public.SP_B3_HIST ();
    1. Checking the data in table temp1
    SELECT * FROM temp1;

    Thank you.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search