If you have n no.of columns in a table, and if you need to select only m no.of columns from that then below is the procedure for you.
CREATE OR REPLACE FUNCTION XCEPT(TABNAME TEXT,VARIADIC COLNAMES TEXT[]) RETURNS VOID AS $$
DECLARE
CHEK INT:=0;
CHEK1 INT:=0;
NO_OF_COLS INT:=0;
ALLCOLS TEXT:=' ';
LC INT:=1;
I RECORD;
J INT;
PR RECORD;
QUERY TEXT:=' ';
NX TEXT;
BEGIN
SELECT 1::INT INTO CHEK
FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME=LOWER(TABNAME);
SELECT COUNT(*) INTO NO_OF_COLS FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=LOWER(TABNAME);
IF (CHEK=1) THEN
FOR I IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=LOWER(TABNAME)) LOOP
FOR J IN 1..NO_OF_COLS LOOP
IF (LOWER(COLNAMES[J])::TEXT=LOWER(I.COLUMN_NAME)) THEN
CHEK1:=1;
END IF;
END LOOP;
IF(CHEK1=0) THEN
ALLCOLS:=ALLCOLS||I.COLUMN_NAME||',';
END IF;
CHEK1:=0;
END LOOP;
RAISE NOTICE '%','SELECT '||TRIM(ALLCOLS,',')|| ' FROM '||TABNAME||';';
END IF;
END;
$$ LANGUAGE 'PLPGSQL';
EMP
-----------
postgres=# \d emp
Table "public.emp"
Column | Type | Modifiers
--------+---------+-----------
eno | integer |
ename | text |
sal | real |
loc | text |
desig | text |
OUTPUT
-----------
postgres=# SELECT XCEPT('emp','loc','eno');
NOTICE: SELECT ename,sal,desig FROM emp;
CREATE OR REPLACE FUNCTION XCEPT(TABNAME TEXT,VARIADIC COLNAMES TEXT[]) RETURNS VOID AS $$
DECLARE
CHEK INT:=0;
CHEK1 INT:=0;
NO_OF_COLS INT:=0;
ALLCOLS TEXT:=' ';
LC INT:=1;
I RECORD;
J INT;
PR RECORD;
QUERY TEXT:=' ';
NX TEXT;
BEGIN
SELECT 1::INT INTO CHEK
FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME=LOWER(TABNAME);
SELECT COUNT(*) INTO NO_OF_COLS FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=LOWER(TABNAME);
IF (CHEK=1) THEN
FOR I IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=LOWER(TABNAME)) LOOP
FOR J IN 1..NO_OF_COLS LOOP
IF (LOWER(COLNAMES[J])::TEXT=LOWER(I.COLUMN_NAME)) THEN
CHEK1:=1;
END IF;
END LOOP;
IF(CHEK1=0) THEN
ALLCOLS:=ALLCOLS||I.COLUMN_NAME||',';
END IF;
CHEK1:=0;
END LOOP;
RAISE NOTICE '%','SELECT '||TRIM(ALLCOLS,',')|| ' FROM '||TABNAME||';';
END IF;
END;
$$ LANGUAGE 'PLPGSQL';
EMP
-----------
postgres=# \d emp
Table "public.emp"
Column | Type | Modifiers
--------+---------+-----------
eno | integer |
ename | text |
sal | real |
loc | text |
desig | text |
OUTPUT
-----------
postgres=# SELECT XCEPT('emp','loc','eno');
NOTICE: SELECT ename,sal,desig FROM emp;
oye all the best for your project. and all your dream comes true. May this project help for the upcomming students who are in your platform. I suggest also to keep some notes or some shortcuts to understand very well for somebody like me
ReplyDelete