-- Function: dba.gtradmin_trunctable(character, character varying)
-- DROP FUNCTION dba.gtradmin_trunctable(character, character varying);
CREATE OR REPLACE FUNCTION dba.gtradmin_trunctable(sn character, tbl character varying)
RETURNS character varying AS
$BODY$
DECLARE
result boolean;
mycaller pg_stat_activity.usename%TYPE;
privilege_grp pg_roles.rolname%TYPE := 'grp_gtradmin_truncate_table_dev';
sql varchar;
err varchar;
snTbl varchar := sn||'.'||tbl;
BEGIN
-- Begin
-- exception when others then raise notice '% %', SQLCODE, SQLERRM;
-- end;
IF EXISTS (select 1 FROM pg_catalog.pg_tables where schemaname='dba' and tablename='log_grp_gtradmin_truncate_table')
THEN null ;
ELSE create table dba.log_grp_gtradmin_truncate_table (usename char(20), trucated_by char(20), truncate_time timestamp, tablename varchar(200));
END IF;
SELECT usename into mycaller FROM pg_catalog.pg_stat_activity WHERE procpid = pg_backend_pid();
IF EXISTS (select g.rolname as Group, m.rolname as Members from pg_auth_members a inner join pg_roles g on g.oid=a.roleid inner join pg_roles m on m.oid=a.member where g.rolname = privilege_grp and m.rolname=mycaller)
THEN
IF NOT EXISTS (select relname,relnamespace,reltype,relowner from pg_class c join pg_namespace n ON n.oid = c.relnamespace join pg_roles r on c.relowner=r.oid where r.rolname='gtradmin_dev' and c.relkind='r' and n.nspname = lower(sn) and c.relname= lower(tbl))
THEN
Result:= false;
err:= 'Table: ' || sn ||'.'||tbl||' does not exist for owner '|| 'gtradmin_dev';
ELSE
--EXEC SQL BEGIN DECLARE SECTION;
--const char *stmt = "CREATE USER ? with password ? in role ?";
--EXEC SQL END DECLARE SECTION;
--EXEC SQL PREPARE mystmt FROM :stmt;
--EXEC SQL EXECUTE mystmt USING username, pwd, rolename;
--EXEC SQL EXECUTE IMMEDIATE :stmt;
sql:='truncate table '||sn||'.'||tbl;
EXECUTE sql;-- USING sn, tbl
result:= true;
END IF;
ELSE
IF Exists (select true
from pg_auth_members a
inner join pg_roles g on g.oid=a.roleid
where g.rolname=privilege_grp and
a.member in (select roleid from pg_auth_members am inner join pg_roles ro on am.member=ro.oid where ro.rolname=mycaller)
)
THEN
IF NOT EXISTS (select relname,relnamespace,reltype,relowner from pg_class c join pg_namespace n ON n.oid = c.relnamespace join pg_roles r on c.relowner=r.oid where r.rolname='gtradmin_dev' and c.relkind='r' and n.nspname = lower(sn) and c.relname= lower(tbl))
THEN
Result:= false;
err:= 'Table: ' || sn ||'.'||tbl||' does not exist for owner -'|| 'gtradmin_dev';
ELSE
sql:='truncate table '||sn||'.'||tbl;
EXECUTE sql;-- USING sn, tbl
result:= true;
END IF;
ELSE
result := false;
err:= 'User: '|| mycaller || ' is not a member of '|| 'grp_gtradmin_truncate_table_dev';
END IF;
END IF;
If result then
Insert into dba.log_grp_gtradmin_truncate_table values ('gtradmin_dev',mycaller,'now', snTbl);
return 'Query Result: ' || snTbl || ' was truncated successfully!';
else return 'Query Result: ' || snTbl || ' truncate failed! Error: '|| err;
end if;
END;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
ALTER FUNCTION dba.gtradmin_trunctable(character, character varying)
OWNER TO gtradmin_dev;
GRANT EXECUTE ON FUNCTION dba.gtradmin_trunctable(character, character varying) TO public;
GRANT EXECUTE ON FUNCTION dba.gtradmin_trunctable(character, character varying) TO gtradmin_dev;
GRANT EXECUTE ON FUNCTION dba.gtradmin_trunctable(character, character varying) TO gtradmin;
calling gp truncate
Moderators: chulett, rschirm, roy
