pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: Robert Treat <xzilla@users.sourceforge.net>
To: greg@turnstep.com
Cc: pgsql-sql@postgresql.org
Subject: Re: obtuse plpgsql function needs
Date: 23 Jul 2003 16:01:53 -0400
Message-ID: <1058990513.22260.698.camel@camel> (raw )
In-Reply-To: <69939b465aaab0cbd79000ce42891f58@biglumber.com >
References: <69939b465aaab0cbd79000ce42891f58@biglumber.com >
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
view thread (14+ messages) latest in thread
Message-ID: <1058990513.22260.698.camel@camel>
Permalink: ../1058990513.22260.698.camel@camel/
Also on: postgresql.org/message-id/1058990513.22260.698.camel@camel
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: xzilla@users.sourceforge.net, greg@turnstep.com
Subject: Re: obtuse plpgsql function needs
In-Reply-To: <1058990513.22260.698.camel@camel>
* 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