truncate table based on passed arguments in postgresql

Question

As a follow up of this question Create FUNCTION in PostgreSQL from a Bash script I tried to extend it with my own parameters so that I can call this functions later in my shell script like that docker exec $CONTAINER_NAME psql -U dev -d $DB_DATABASE -v ON_ERROR_STOP=1 -c "select $DB_SCHEMA.truncate_tables('$DB_USERNAME','$DB_DATABASE');" .


 CREATE
OR REPLACE FUNCTION truncate_tables(dbUserName text, dbSchema text) RETURNS void LANGUAGE plpgsql AS $function$
  DECLARE
statements CURSOR FOR
SELECT tablename
FROM pg_tables
WHERE tableowner = dbUserName
  AND schemaname = dbSchema
  AND tablename not like 'flyway%';
BEGIN
FOR stmt IN statements LOOP
          EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ';';
END LOOP;
END
  $function$;

So far my shell script runs but it doesn't truncate anything nor it fails.

When I hardcode values however it runs perfectly. I think I am missing smth from postgres syntax here.

SELECT tablename
FROM pg_tables
WHERE tableowner = 'dev'
  AND schemaname = 'mySchema'
  AND tablename not like 'flyway%';

UPD1:

Added schema name to a truncation, but still nothing happens (no failures either)

 create OR REPLACE FUNCTION truncate_tables(dbUserName IN varchar, dbSchema in varchar) 
 RETURNS void LANGUAGE plpgsql AS $function$
  DECLARE
statements CURSOR FOR
SELECT tablename FROM pg_tables
WHERE tableowner = dbUserName
  AND schemaname = dbSchema
  AND tablename not like 'flyway%';
begin
    raise notice 'Value: %', statements;
FOR stmt IN statements LOOP
          EXECUTE format('TRUNCATE %i.TABLE ' || quote_ident(stmt.tablename) || ';', dbSchema);
END LOOP;
END
  $function$;

UPD 2: Another attempt, but still data is not truncated :/

CREATE OR REPLACE FUNCTION truncate_tables(dbUserName IN varchar, dbSchema in varchar) RETURNS VOID AS $$
DECLARE
    v_parent_rec RECORD;
BEGIN
    FOR v_parent_rec IN SELECT tablename FROM pg_tables
        WHERE tableowner = dbUserName
        AND schemaname = dbSchema
        AND tablename not like 'flyway%' loop
        PERFORM  format('TRUNCATE %I.TABLE ' || quote_ident(v_parent_rec) || ';', dbSchema);
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

Print the contents of the cursor to the console (raise notice / return / etc) before running execute to check that the syntax is correct (e.g. no problem with apostrophes) and the input parameters are being used (and not NULL) — Jesusbrother
– Jesusbrother, Commented Jun 13, 2022 at 16:39
The immediate problem I see is that you're checking tables in schema dbSchema, but are truncating without schema name. — eijeze
– eijeze, Commented Jun 13, 2022 at 16:44
@Jesusbrother added ...begin raise notice 'Value: %', statements; and got : Value: statements — Ren
– Ren, Commented Jun 13, 2022 at 16:45
Also, your code seems to use stmt variable. which is not defined. I guess your function "works" (as opposed to errors out) simply because you're using cursor, and the iteration on it, should be done differently. — eijeze
– eijeze, Commented Jun 13, 2022 at 16:51
@eijeze could you please elaborate on that? btw adding schema name to a truncation didn't work either — Ren
– Ren, Commented Jun 13, 2022 at 16:53

eijeze · Accepted Answer · 2022-06-13 16:54:15Z

1

This should, I think, work:

CREATE OR REPLACE FUNCTION truncate_tables(dbUserName text, dbSchema text)
    RETURNS void LANGUAGE plpgsql AS
$function$
DECLARE
    v_sql TEXT;
BEGIN
    FOR v_sql IN SELECT format('TRUNCATE TABLE %I.%I', schemaname, tablename)
        FROM pg_tables
        WHERE tableowner = dbUserName AND schemaname = dbSchema AND tablename NOT like 'flyway%'
    LOOP
        raise notice 'Will try to do: %', v_sql;
        -- If notices look sane, uncomment next line:
        -- EXECUTE v_sql;
    END LOOP;
    END;
$function$;

answered Jun 13, 2022 at 16:54

eijeze

1513 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ren Over a year ago

Still not working :/

eijeze Over a year ago

What do you mean "not working"? What did it output?

Ren Over a year ago

There is empty output unfortunately and data is still in. I have attached another version. @eijeze

Ren Over a year ago

Turned out that I had a typo in my db name, now everything worked! Thanks!

Collectives™ on Stack Overflow

truncate table based on passed arguments in postgresql

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related