The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Why this procedure does not insert data into the table, although it is performed successfully, I can

AnelyaAnelya Community Edition User
edited May 4 in General Discussion

Procedure on PLvSQL:

CREATE OR REPLACE PROCEDURE max_load()
AS $$
DECLARE
table_name_1 varchar(64);
load_date_col varchar(64);
max_date varchar(64);
BEGIN
FOR table_name_1 IN QUERY
SELECT table_name
FROM v_catalog.columns
WHERE
table_schema = 'public' and column_name LIKE '%LOAD_DATE%'
ORDER BY table_name
LOOP
BEGIN
SELECT column_name INTO load_date_col
FROM v_catalog.columns
WHERE table_name = table_name_1 AND column_name LIKE '%LOAD_DATE%';
IF load_date_col IS NULL THEN
RAISE EXCEPTION 'table % doesn't have column like LOAD_DATE', table_name_1;
END IF;
max_date := EXECUTE 'SELECT MAX($1) FROM public.$2' USING load_date_col, table_name_1 ;
IF max_date IS NULL THEN
max_date := NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
max_date := NULL;
END;
EXECUTE 'INSERT INTO max_load_dates VALUES (''$1'', ''$2'', coalesce($3::varchar, ''null'') )' USING table_name_1, load_date_col, max_date;
END LOOP;
END;
$$ LANGUAGE PLvSQL;

Answers

  • Bryan_HBryan_H Vertica Employee Administrator

    My recollection is that stored procedures do not auto-commit. Add something like PERFORM EXECUTE COMMIT; after END LOOP but before END of procedure.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file