skip to Main Content

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?

2

Answers


  1. 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. 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