Re: obtuse plpgsql function needs

-----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_class WHERE 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 FROM pg_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(\',\' IN format_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(at)turnstep(dot)com
PGP Key: 0x14964AC8 200307231536
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

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