Re: How the get variables out of a plggsql RECORD by column number

--- Tjibbe Rijpma <t.b.rijpma@student.tudelft.nl>
wrote:

> Yes I'm looking for a generic function, because I do
> not know the
> fieldnames.
>
> Is there no possibility to insert the query result
> into a ARRAY and then
> select _result[0][0]? (I didn't get it work.....)
>
> Or to get the fieldnames?

You could query the system catalogs.  I haven't tried
this, but: a query like "select column_name into
columnnamevariable from information_schema.columns
where table_name = whatever and ordinal_position = 1"
should get you what you want.

>
> Pl/tcl, pl/python or pl/perl are not installed by my
> provider and I have no
> experience with it.
>
>
> Tjibbe
> ----- Original Message -----
> From: "Jeff Eckermann" <jeff_eckermann@yahoo.com>
> To: "Tjibbe Rijpma" <t.b.rijpma@student.tudelft.nl>;
> <pgsql-novice@postgresql.org>
> Sent: Tuesday, January 18, 2005 15:41
> Subject: Re: [NOVICE] How the get variables out of a
> plggsql RECORD by
> column number
>
>
> > --- Tjibbe Rijpma <t.b.rijpma@student.tudelft.nl>
> > wrote:
> >
> > > A record type contians the 1st row form dynamic
> > > query result.
> > >
> > > From that row I want to select the 1st column.
> > >
> > > How do I do that?
> >
> > By name, i.e. "_row.firstfieldname".  Perhaps you
> are
> > looking for a generic function, that can be
> applied to
> > any table?  Pl/pgsql is not equipped to do that.
> I
> > believe that you can achieve what you want with
> > pl/tcl, pl/python or pl/perl (not sure about the
> > last).
> >
> > >
> > >
> > > CREATE OR REPLACE FUNCTION dswz.save_query
> (TEXT)
> > > RETURNS BOOL AS '
> > > DECLARE
> > > _query ALIAS FOR $1;
> > > _temp_query TEXT;
> > > _row RECORD;
> > > _id INT8;
> > > _type INT8;
> > >
> > > BEGIN
> > > /* select the 1st row*/
> > > _temp_query := ''SELECT * FROM ('' || _query ||
> '')
> > > AS table_source LIMIT 1'';
> > >
> > > FOR _row IN EXECUTE temp_query LOOP
> > >
> > >     /* select the 1st column */
> > >     _id :=  ????_row[0]??????
> > >
> > > END LOOP;
> > >
> > > /* Saves the query and his type */
> > > SELECT INTO _type type FROM objects WHERE id =
> _id;
> > > INSERT INTO queries VALUES (_query, _type);
> > >
> > >
> > > RETURN TRUE;
> > > END
> > >
> > > ' LANGUAGE 'plpgsql' SECURITY DEFINER;
> >
> >
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > Yahoo! Mail - now with 250MB free storage. Learn
> more.
> > http://info.mail.yahoo.com/mail_250
> >
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
>       subscribe-nomail command to
> majordomo@postgresql.org so that your
>       message can get through to the mailing list
> cleanly
>




__________________________________
Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.
http://promotions.yahoo.com/new_mail