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
You can loop to run a separate
INSERT
per source table. (Like for big tables andCOMMIT
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:
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.
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.
Thank you.