X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id F3C9ED1C4DF
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 23 Jul 2003 19:38:48 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 56504-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 23 Jul 2003 16:38:07 -0300 (ADT)
Received: from biglumber.com (biglumber.com [207.228.252.42])
	by svr1.postgresql.org (Postfix) with SMTP id 08CD8D1C4C1
	for <pgsql-sql@postgresql.org>; Wed, 23 Jul 2003 16:38:47 -0300 (ADT)
Received: (qmail 315 invoked from network); 23 Jul 2003 19:38:48 -0000
Received: from biglumber.com (HELO localhost) (207.228.252.42)
	by 0 with SMTP; 23 Jul 2003 19:38:48 -0000
From: greg@turnstep.com
To: pgsql-sql@postgresql.org
Subject: Re: obtuse plpgsql function needs
X-PGP-Key: 2529 DF6A B8F7 9407 E944  45B4 BC9B 9067 1496 4AC8
X-Request-PGP: 
 http://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
In-Reply-To: <1058913112.22259.271.camel@camel>
Date: Wed, 23 Jul 2003 19:38:47 -0000
X-Mailer: JoyMail 1.37
Message-ID: <69939b465aaab0cbd79000ce42891f58@biglumber.com>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/345
X-Sequence-Number: 14459


-----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@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-----