truncate table based on passed arguments in postgresql-CodePudding

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;

CodePudding user response：

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