Re: obtuse plpgsql function needs - Mailing list pgsql-sql

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1



Perhaps something like this?. Called like thus:

SELECT a,b,c,msgmaker('t1',ctid) FROM t1 WHERE a=b;


CREATE OR REPLACE FUNCTION msgmaker(text,tid) RETURNS text AS '

DECLARE
 mytable ALIAS FOR $1; mytid   ALIAS FOR $2; myctid  TEXT;
 myquery TEXT; mylen   SMALLINT := 20; yourlen SMALLINT; mydec   SMALLINT; myinfo  TEXT; myrec   RECORD; biglist TEXT
:=\'Error\';
 

BEGIN
 myquery := \'   SELECT length(attname) AS lenny FROM pg_attribute   WHERE attnum >=1   AND attrelid = (SELECT oid FROM
pg_classWHERE relname = \'\'\' || mytable || \'\'\')   ORDER BY 1 DESC LIMIT 1\';
 
 FOR myrec IN EXECUTE myquery LOOP   mylen := myrec.lenny; END LOOP;
 myquery := \'   SELECT attname, atttypid, atttypmod FROM pg_attribute   WHERE attnum >=1   AND attrelid = (SELECT oid
FROMpg_class WHERE relname = \'\'\' || mytable || \'\'\')   ORDER BY attname ASC\';
 
 myinfo := \'SELECT \';
 FOR myrec IN EXECUTE myquery LOOP   myinfo := myinfo || \'\'\'- \' || myrec.attname || \': \';   yourlen :=
LENGTH(myrec.attname);  LOOP     myinfo := myinfo || \' \';     yourlen := yourlen + 1;     EXIT WHEN yourlen > mylen;
END LOOP;   myinfo := myinfo || \'\'\' || COALESCE(\';   IF myrec.atttypid = 1184 THEN     myinfo := myinfo ||
\'TO_CHAR(\'|| myrec.attname || \',\'\'Mon DD, YYYY HH24:MI\'\')\';   ELSIF myrec.atttypid = 16 THEN     myinfo :=
myinfo|| \'CASE WHEN \' || myrec.attname || \' IS TRUE THEN \'\'True\'\' ELSE \'\'False\'\' END\';   ELSIF
myrec.atttypid= 17 THEN     myinfo := myinfo || \'ENCODE(\' || myrec.attname || \',\'\'hex\'\')\';   ELSIF
myrec.atttypid= 1700 THEN     SELECT substr(rtrim(format_type(myrec.atttypid, myrec.atttypmod),\')\'), position(\',\'
INformat_type(myrec.atttypid, myrec.atttypmod))+1) INTO mydec;     myinfo := myinfo || \'TO_CHAR(\' || myrec.attname ||
\',\'\'FM99999999990\';    IF mydec > 1 THEN       myinfo := myinfo || \'.\';       LOOP         myinfo := myinfo ||
\'0\';        mydec := mydec - 1;         EXIT WHEN mydec < 1;       END LOOP;     END IF;     myinfo := myinfo ||
\'\'\')\';  ELSE      myinfo := myinfo || myrec.attname;   END IF;   myinfo := myinfo || \'::text,\'\'<null>\'\'::text)
||\'\'\\\\n\'\' || \\n\'; END LOOP;
 
 SELECT mytid INTO myctid;
 myinfo := myinfo || \'\'\'\\\\n\'\' AS info FROM \' || mytable || \' WHERE ctid = \'\'\' || myctid || \'\'\'\';
 FOR myrec IN EXECUTE myinfo LOOP   biglist := myrec.info; END LOOP;
 RETURN biglist;

END;

' LANGUAGE 'plpgsql';




- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200307231536
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE/HuPCvJuQZxSWSsgRAnNsAJ9Qljeo+2NkBIp17TKb6SRf2T6WwACg8bwV
A2TBRJdMzk0jpw67sIk3+uc=
=cjEZ
-----END PGP SIGNATURE-----