问题描述:

I'm trying to create a PostgreSQL stored procedure that must set default value 'now()' to each columns named 'CreationDate' in every tables.

CREATE OR REPLACE FUNCTION set_creation_date() RETURNS

void AS $$

DECLARE

t pg_tables%ROWTYPE;

BEGIN

FOR t IN SELECT "tablename" FROM pg_tables WHERE "schemaname" = 'public' LOOP

IF EXISTS (select * from information_schema.columns where table_name = t."tablename"

and column_name = 'CreationDate') THEN

EXECUTE FORMAT('ALTER TABLE %I ALTER COLUMN "CreationDate" SET DEFAULT now()', t."tablename");

END IF;

END LOOP;

END;

$$ LANGUAGE plpgsql;

But no columns are affected. What's wrong?

网友答案:

It looks like it's trying to alter t."tablename" every time. Try the following instead:

EXECUTE FORMAT('ALTER TABLE %I ALTER COLUMN "CreationDate" SET DEFAULT now()', t."tablename");

I don't have a PostgreSQL server available to test, so please let me know if the syntax isn't quite right.

网友答案:

I finally resolved. The error was in pg_tables%ROWTYPE: using debugger i realized that the field 'tablename' doesn't exist, so i declared 't' as text. I created a function with parameters, where you have to pass schema name and column name. I ll post it here for anyone who ll need it

    DROP FUNCTION IF EXISTS set_creation_date(character varying, character varying);

CREATE OR REPLACE FUNCTION set_creation_date(_schema_name character varying, _column_date character varying)
  RETURNS void AS
$BODY$
DECLARE
     t TEXT;
BEGIN
     FOR t IN SELECT "tablename" FROM pg_tables WHERE "schemaname" = _schema_name  LOOP
     IF EXISTS (select * from information_schema.columns where table_name = t and column_name = _column_date) THEN
    EXECUTE FORMAT('ALTER TABLE %I ALTER COLUMN %I SET DEFAULT now()', t, _column_date);
    END IF;
     END LOOP;
END;
$BODY$
  LANGUAGE plpgsql;
相关阅读:
Top