Re: obtuse plpgsql function needs

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(at)turnstep(dot)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