Re: [SQL] obtuse plpgsql function needs

[greg]

-----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 [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200307231536
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

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



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match