Execute dynamic inserts in a procedure

Question

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?

For dynamic statements (i.e. a dynamic table name) you need to use EXECUTE. Check here: postgresql.org/docs/current/… Also, please end your statements in PL/pgSQL with a semicolon. Last, I cannot help but please, Postgres or PostgreSQL, not PostGRES - this is really triggering me. — Islingre
– Islingre, Commented Oct 4, 2024 at 1:19
@LaurenzAlbe. I think it is due to AS$$ missing space between AS and $$. — user7070613
– user7070613, Commented Oct 4, 2024 at 16:25

Erwin Brandstetter · Accepted Answer · 2024-10-04 02:21:31Z

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();

Is there a shortcut for SELECT * FROM?

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.

Are PostgreSQL column names case-sensitive?

Abu Ben Reaz · Accepted Answer · 2024-10-04 06:23:27Z

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.

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)
);

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; $$;

Finally Calling the procedure for execution

call public.SP_B3_HIST ();

Checking the data in table temp1

SELECT * FROM temp1;

Thank you.

Collectives™ on Stack Overflow

Execute dynamic inserts in a procedure

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related