pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: greg@turnstep.com
To: pgsql-sql@postgresql.org
Subject: Re: obtuse plpgsql function needs
Date: Wed, 23 Jul 2003 19:38:47 -0000
Message-ID: <69939b465aaab0cbd79000ce42891f58@biglumber.com> (raw)
In-Reply-To: <1058913112.22259.271.camel@camel>


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

view thread (14+ messages)  latest in thread

Message-ID: <69939b465aaab0cbd79000ce42891f58@biglumber.com>
Permalink:  ../69939b465aaab0cbd79000ce42891f58@biglumber.com/
Also on:    postgresql.org/message-id/69939b465aaab0cbd79000ce42891f58@biglumber.com

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: greg@turnstep.com
  Subject: Re: obtuse plpgsql function needs
  In-Reply-To: <69939b465aaab0cbd79000ce42891f58@biglumber.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox