Subject: Re: obtuse plpgsql function needs
From: Robert Treat <xzilla@users.sourceforge.net>
To: greg@turnstep.com
Cc: pgsql-sql@postgresql.org
In-Reply-To: <69939b465aaab0cbd79000ce42891f58@biglumber.com>
References: <69939b465aaab0cbd79000ce42891f58@biglumber.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 23 Jul 2003 16:01:53 -0400
Message-Id: <1058990513.22260.698.camel@camel>
Mime-Version: 1.0

Questions for the group:

1) any way to do this without the ctid/oid? Sounds like I could do
select a,b,msgmaker(*) from t1 where a=b; in pltcl (which was an early
inclination I abandoned, perhaps prematurely)

2) would it be faster in pltcl? seems like it would if i didn't have to
do the catalog lookups, but is pltcl inherently faster anyways?

thanks for the input so far.

Robert Treat

On Wed, 2003-07-23 at 15:38, greg@turnstep.com wrote:
> 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';
> 
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL