0

I have a pgscript that runs in a loop.

SET @id = 1;
BEGIN
WHILE @id <= 10

/*
CREATE TABLE tbl_name AS
SELECT * FROM main_tbl
WHERE id = @id
;

INSERT INTO tbl_name
SELECT * FROM main_tbl
WHERE id = @id
*/

SET @id = @id + 1;

END

For the first iteration id=1, I want my script to create a table because the table does not exist. For the upcoming loop id=2...3...4...so on, I want the script to insert into the table that was created.

Currently, I am creating an empty table before running the PgScript. Is there a way to write a script that creates the table for the first loop and insert in the upcoming iterations?

Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
Garfield
  • 143
  • 11
  • You should make it clear in your question that you are talking about a feature, `PgScript`, that is `pgAdmin` specific. – Adrian Klaver Dec 22 '20 at 18:19
  • 2
    Why not create the table before the loop? – Adrian Klaver Dec 22 '20 at 18:21
  • "*Currently, I am creating an empty table before running the PgScript*" - what's wrong with that? You also don't need pgScript or even a loop to do what you want. A simple `insert into target_table select * from some_table where id between 1 and 10` will be much more efficient. Plus it would work with any SQL client –  Dec 22 '20 at 18:49

1 Answers1

0

Try this:

CREATE TABLE IF NOT EXISTS words (
    id SERIAL PRIMARY KEY,
    word VARCHAR(500) NOT NULL
);

INSERT INTO words
        VALUES(DEFAULT, '1234');

SELECT *
FROM words;
Allan Santos
  • 19
  • 5