Thread: How the get variables out of a plggsql RECORD by column number
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?
CREATE OR REPLACE FUNCTION dswz.save_query (TEXT) RETURNS BOOL AS '
DECLARE
_query ALIAS FOR $1;
_temp_query TEXT;
_row RECORD;
_id INT8;
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;
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
END
' LANGUAGE 'plpgsql' SECURITY DEFINER;
--- 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
> > 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?
>
Can you use a simple LOOP?
DECLARE rcrd_data RECORD
FOR rcrd_data IN SELECT tbl_source.first_column,
tbl_source.second_column,
FROM tbl_source
LOOP
-- Now rcrd_data.first_column has data.
END LOOP;
See
http://www.postgresql.org/docs/7.4/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
for more information.
Kind Regards,
Keith
PS Caution, I am still a novice myself.
______________________________________________
99main Internet Services http://www.99main.com
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?
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
>
--- 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