pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql Question..
14+ messages / 8 participants
[nested] [flat]
* plpgsql Question..
@ 2003-04-28 12:12 Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 1 reply; 14+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-28 12:12 UTC (permalink / raw)
To: pgsql-sql
is it possible to access a feild in a RECORD type
variable where the feild name is variable.
eg say r is a record having feild name , email , salary
r.name is 'foo'
r.email is 'bar@foo.com'
r.salary is 1000
suppose feild_name iterates via a FOR LOOP
through values (name , email , salary)
is it possible to access that feild in record r
inside the loop ?
Regds
mallah.
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: plpgsql Question..
@ 2003-04-28 13:00 Rod Taylor <rbt@rbt.ca>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 2 replies; 14+ messages in thread
From: Rod Taylor @ 2003-04-28 13:00 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-sql
On Mon, 2003-04-28 at 08:12, Rajesh Kumar Mallah wrote:
> is it possible to access a feild in a RECORD type
> variable where the feild name is variable.
>
> eg say r is a record having feild name , email , salary
>
> r.name is 'foo'
> r.email is 'bar@foo.com'
> r.salary is 1000
>
> suppose feild_name iterates via a FOR LOOP
> through values (name , email , salary)
>
> is it possible to access that feild in record r
> inside the loop ?
Not that I'm aware of. What you can do it build the query to SELECT
<variable> FROM table.
Another alternative is to switch languages. A perl, tcl, etc. based
function would be capable of doing what you want.
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: plpgsql Question..
@ 2003-04-28 13:23 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Rod Taylor <rbt@rbt.ca>
1 sibling, 0 replies; 14+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-28 13:23 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: pgsql-sql
Thanks rod ,
i am looking into the possibilities.
Regds
mallah.
On Monday 28 Apr 2003 6:30 pm, Rod Taylor wrote:
> On Mon, 2003-04-28 at 08:12, Rajesh Kumar Mallah wrote:
> > is it possible to access a feild in a RECORD type
> > variable where the feild name is variable.
> >
> > eg say r is a record having feild name , email , salary
> >
> > r.name is 'foo'
> > r.email is 'bar@foo.com'
> > r.salary is 1000
> >
> > suppose feild_name iterates via a FOR LOOP
> > through values (name , email , salary)
> >
> > is it possible to access that feild in record r
> > inside the loop ?
>
> Not that I'm aware of. What you can do it build the query to SELECT
> <variable> FROM table.
>
> Another alternative is to switch languages. A perl, tcl, etc. based
> function would be capable of doing what you want.
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: plpgsql Question..
@ 2003-04-28 13:31 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Rod Taylor <rbt@rbt.ca>
1 sibling, 1 reply; 14+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-28 13:31 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: pgsql-sql
Hi,
I wanted to "merge" two records in a table
having lots of feilds i dont want to modify
the procedure every time a add a new record.
so i pass the function two primary keys to be
merged (source,dest) , the function
1. shud iterate the list of feilds in that tables .
2. For each feild compare the two values in the rows and pick the not
null one from either of the two.
3. update dest row with the not null values derieved from source if
dest were null.
the following is my unsuccessful attempt to this
requirement.
CREATE OR REPLACE FUNCTION general.merge_profiles (integer,integer) RETURNS text AS '
DECLARE
source alias for $1;
dest alias for $2;
source_record RECORD;
dest_record RECORD;
r RECORD;
upd_stmt text;
BEGIN
SELECT INTO source_record * from general.profile_master where profile_id=source;
IF NOT FOUND THEN
RAISE EXCEPTION '' profile % not found '' , source;
END IF;
SELECT INTO dest_record * from general.profile_master where profile_id=dest;
IF NOT FOUND THEN
RAISE EXCEPTION '' profile % not found '' , dest;
END IF;
upd_stmt := '' UPDATE profile_master SET '' ;
FOR r IN SELECT get_columns as colname from utils.get_columns(''profile_master'' , ''general'')
where get_columns not in (''profile_id'') LOOP
upd_stmt := upd_stmt || r.colname ||
''= COALESCE (source_record.'' || r.colname || '', dest_record.'' || r.colname || '') , '' ;
END LOOP ;
upd_stmt := rtrim (upd_stmt , '', '');
upd_stmt := upd_stmt || '' WHERE profile_id = '' || dest || '';'' ;
-- PERFORM upd_stmt;
RAISE INFO ''%'' , upd_stmt;
RETURN ''OK'';
END;
' LANGUAGE 'plpgsql' ;
On Monday 28 Apr 2003 6:30 pm, you wrote:
> On Mon, 2003-04-28 at 08:12, Rajesh Kumar Mallah wrote:
> > is it possible to access a feild in a RECORD type
> > variable where the feild name is variable.
> >
> > eg say r is a record having feild name , email , salary
> >
> > r.name is 'foo'
> > r.email is 'bar@foo.com'
> > r.salary is 1000
> >
> > suppose feild_name iterates via a FOR LOOP
> > through values (name , email , salary)
> >
> > is it possible to access that feild in record r
> > inside the loop ?
>
> Not that I'm aware of. What you can do it build the query to SELECT
> <variable> FROM table.
>
> Another alternative is to switch languages. A perl, tcl, etc. based
> function would be capable of doing what you want.
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: plpgsql Question..
@ 2003-04-28 13:46 Rod Taylor <rbt@rbt.ca>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 2 replies; 14+ messages in thread
From: Rod Taylor @ 2003-04-28 13:46 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-sql
Why select at all? Just update them both with the below?
UPDATE desttable SET <field> = coalesce(<field>, (SELECT <field> FROM
sourcetable)), <field2> = coalesce(<field2>, (SELECT <field2> FROM
sourcetable));
This will update field to be the old value of field, if one existed, or
the value from the sourcetable if it was null.
If speed is an issue, look into an UPDATE with the FROM clause to do a
join rather than several sub-selects.
Build the above query by passing the function a list of fields via
get_columns() as used below.
On Mon, 2003-04-28 at 09:31, Rajesh Kumar Mallah wrote:
> Hi,
>
> I wanted to "merge" two records in a table
> having lots of feilds i dont want to modify
> the procedure every time a add a new record.
>
> so i pass the function two primary keys to be
> merged (source,dest) , the function
>
>
> 1. shud iterate the list of feilds in that tables .
> 2. For each feild compare the two values in the rows and pick the not
> null one from either of the two.
>
> 3. update dest row with the not null values derieved from source if
> dest were null.
>
>
>
> the following is my unsuccessful attempt to this
> requirement.
>
>
>
> CREATE OR REPLACE FUNCTION general.merge_profiles (integer,integer) RETURNS text AS '
>
> DECLARE
> source alias for $1;
> dest alias for $2;
> source_record RECORD;
> dest_record RECORD;
> r RECORD;
> upd_stmt text;
>
>
> BEGIN
>
> SELECT INTO source_record * from general.profile_master where profile_id=source;
>
> IF NOT FOUND THEN
> RAISE EXCEPTION '' profile % not found '' , source;
> END IF;
>
> SELECT INTO dest_record * from general.profile_master where profile_id=dest;
>
> IF NOT FOUND THEN
> RAISE EXCEPTION '' profile % not found '' , dest;
> END IF;
>
> upd_stmt := '' UPDATE profile_master SET '' ;
>
> FOR r IN SELECT get_columns as colname from utils.get_columns(''profile_master'' , ''general'')
> where get_columns not in (''profile_id'') LOOP
> upd_stmt := upd_stmt || r.colname ||
> ''= COALESCE (source_record.'' || r.colname || '', dest_record.'' || r.colname || '') , '' ;
>
> END LOOP ;
>
> upd_stmt := rtrim (upd_stmt , '', '');
>
> upd_stmt := upd_stmt || '' WHERE profile_id = '' || dest || '';'' ;
>
> -- PERFORM upd_stmt;
> RAISE INFO ''%'' , upd_stmt;
>
> RETURN ''OK'';
> END;
>
> ' LANGUAGE 'plpgsql' ;
>
>
>
>
> On Monday 28 Apr 2003 6:30 pm, you wrote:
> > On Mon, 2003-04-28 at 08:12, Rajesh Kumar Mallah wrote:
> > > is it possible to access a feild in a RECORD type
> > > variable where the feild name is variable.
> > >
> > > eg say r is a record having feild name , email , salary
> > >
> > > r.name is 'foo'
> > > r.email is 'bar@foo.com'
> > > r.salary is 1000
> > >
> > > suppose feild_name iterates via a FOR LOOP
> > > through values (name , email , salary)
> > >
> > > is it possible to access that feild in record r
> > > inside the loop ?
> >
> > Not that I'm aware of. What you can do it build the query to SELECT
> > <variable> FROM table.
> >
> > Another alternative is to switch languages. A perl, tcl, etc. based
> > function would be capable of doing what you want.
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: plpgsql Question..
@ 2003-04-28 15:05 Rajesh Kumar Mallah <rmallah@trade-india.com>
parent: Rod Taylor <rbt@rbt.ca>
1 sibling, 0 replies; 14+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-28 15:05 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: Rajesh Kumar Mallah <mallah@trade-india.com>; pgsql-sql
On 28 Apr 2003, Rod Taylor wrote:
> Why select at all? Just update them both with the below?
>
> UPDATE desttable SET <field> = coalesce(<field>, (SELECT <field> FROM
> sourcetable)), <field2> = coalesce(<field2>, (SELECT <field2> FROM
> sourcetable));
Hey i dont want to depend on the current list of feilds in the
table rite now. In that case i will have to keep updating
the function when the table structure is altered.
so i wanted to depend on the catalogs for getting list of
feilds.
is my thinking correct ?
regds
mallah.
>
> This will update field to be the old value of field, if one existed, or
> the value from the sourcetable if it was null.
>
> If speed is an issue, look into an UPDATE with the FROM clause to do a
> join rather than several sub-selects.
>
>
> Build the above query by passing the function a list of fields via
> get_columns() as used below.
>
> On Mon, 2003-04-28 at 09:31, Rajesh Kumar Mallah wrote:
> > Hi,
> >
> > I wanted to "merge" two records in a table
> > having lots of feilds i dont want to modify
> > the procedure every time a add a new record.
> >
> > so i pass the function two primary keys to be
> > merged (source,dest) , the function
> >
> >
> > 1. shud iterate the list of feilds in that tables .
> > 2. For each feild compare the two values in the rows and pick the not
> > null one from either of the two.
> >
> > 3. update dest row with the not null values derieved from source if
> > dest were null.
> >
> >
> >
> > the following is my unsuccessful attempt to this
> > requirement.
> >
> >
> >
> > CREATE OR REPLACE FUNCTION general.merge_profiles (integer,integer) RETURNS text AS '
> >
> > DECLARE
> > source alias for $1;
> > dest alias for $2;
> > source_record RECORD;
> > dest_record RECORD;
> > r RECORD;
> > upd_stmt text;
> >
> >
> > BEGIN
> >
> > SELECT INTO source_record * from general.profile_master where profile_id=source;
> >
> > IF NOT FOUND THEN
> > RAISE EXCEPTION '' profile % not found '' , source;
> > END IF;
> >
> > SELECT INTO dest_record * from general.profile_master where profile_id=dest;
> >
> > IF NOT FOUND THEN
> > RAISE EXCEPTION '' profile % not found '' , dest;
> > END IF;
> >
> > upd_stmt := '' UPDATE profile_master SET '' ;
> >
> > FOR r IN SELECT get_columns as colname from utils.get_columns(''profile_master'' , ''general'')
> > where get_columns not in (''profile_id'') LOOP
> > upd_stmt := upd_stmt || r.colname ||
> > ''= COALESCE (source_record.'' || r.colname || '', dest_record.'' || r.colname || '') , '' ;
> >
> > END LOOP ;
> >
> > upd_stmt := rtrim (upd_stmt , '', '');
> >
> > upd_stmt := upd_stmt || '' WHERE profile_id = '' || dest || '';'' ;
> >
> > -- PERFORM upd_stmt;
> > RAISE INFO ''%'' , upd_stmt;
> >
> > RETURN ''OK'';
> > END;
> >
> > ' LANGUAGE 'plpgsql' ;
> >
> >
> >
> >
> > On Monday 28 Apr 2003 6:30 pm, you wrote:
> > > On Mon, 2003-04-28 at 08:12, Rajesh Kumar Mallah wrote:
> > > > is it possible to access a feild in a RECORD type
> > > > variable where the feild name is variable.
> > > >
> > > > eg say r is a record having feild name , email , salary
> > > >
> > > > r.name is 'foo'
> > > > r.email is 'bar@foo.com'
> > > > r.salary is 1000
> > > >
> > > > suppose feild_name iterates via a FOR LOOP
> > > > through values (name , email , salary)
> > > >
> > > > is it possible to access that feild in record r
> > > > inside the loop ?
> > >
> > > Not that I'm aware of. What you can do it build the query to SELECT
> > > <variable> FROM table.
> > >
> > > Another alternative is to switch languages. A perl, tcl, etc. based
> > > function would be capable of doing what you want.
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: plpgsql Question..
@ 2003-04-29 00:14 mallah@trade-india.com
parent: Rod Taylor <rbt@rbt.ca>
1 sibling, 0 replies; 14+ messages in thread
From: mallah@trade-india.com @ 2003-04-29 00:14 UTC (permalink / raw)
To: rbt@rbt.ca; +Cc: pgsql-sql
> Why select at all? Just update them both with the below?
>
> UPDATE desttable SET <field> = coalesce(<field>, (SELECT <field> FROM sourcetable)), <field2> =
> coalesce(<field2>, (SELECT <field2> FROM sourcetable));
>
> This will update field to be the old value of field, if one existed, or the value from the
> sourcetable if it was null.
>
> If speed is an issue, look into an UPDATE with the FROM clause to do a join rather than several
> sub-selects.
Oops Sorry ,
This is indeed a solution i missed to read the last two lines while
posting the previous reply. Thanks a lot i will try and get back.
Regds
Mallah.
>
>
> Build the above query by passing the function a list of fields via get_columns() as used below.
>
> On Mon, 2003-04-28 at 09:31, Rajesh Kumar Mallah wrote:
>> Hi,
>>
>> I wanted to "merge" two records in a table
>> having lots of feilds i dont want to modify
>> the procedure every time a add a new record.
>>
>> so i pass the function two primary keys to be
>> merged (source,dest) , the function
>>
>>
>> 1. shud iterate the list of feilds in that tables .
>> 2. For each feild compare the two values in the rows and pick the not
>> null one from either of the two.
>>
>> 3. update dest row with the not null values derieved from source if
>> dest were null.
>>
>>
>>
>> the following is my unsuccessful attempt to this
>> requirement.
>>
>>
>>
>> CREATE OR REPLACE FUNCTION general.merge_profiles (integer,integer) RETURNS text AS '
>>
>> DECLARE
>> source alias for $1;
>> dest alias for $2;
>> source_record RECORD;
>> dest_record RECORD;
>> r RECORD;
>> upd_stmt text;
>>
>>
>> BEGIN
>>
>> SELECT INTO source_record * from general.profile_master where profile_id=source;
>>
>> IF NOT FOUND THEN
>> RAISE EXCEPTION '' profile % not found '' , source;
>> END IF;
>>
>> SELECT INTO dest_record * from general.profile_master where profile_id=dest;
>>
>> IF NOT FOUND THEN
>> RAISE EXCEPTION '' profile % not found '' , dest;
>> END IF;
>>
>> upd_stmt := '' UPDATE profile_master SET '' ;
>>
>> FOR r IN SELECT get_columns as colname from utils.get_columns(''profile_master'' ,
>> ''general'')
>> where get_columns not in (''profile_id'') LOOP
>> upd_stmt := upd_stmt || r.colname ||
>> ''= COALESCE (source_record.'' || r.colname || '', dest_record.'' || r.colname
>> || '') , '' ;
>>
>> END LOOP ;
>>
>> upd_stmt := rtrim (upd_stmt , '', '');
>>
>> upd_stmt := upd_stmt || '' WHERE profile_id = '' || dest || '';'' ;
>>
>> -- PERFORM upd_stmt;
>> RAISE INFO ''%'' , upd_stmt;
>>
>> RETURN ''OK'';
>> END;
>>
>> ' LANGUAGE 'plpgsql' ;
>>
>>
>>
>>
>> On Monday 28 Apr 2003 6:30 pm, you wrote:
>> > On Mon, 2003-04-28 at 08:12, Rajesh Kumar Mallah wrote:
>> > > is it possible to access a feild in a RECORD type
>> > > variable where the feild name is variable.
>> > >
>> > > eg say r is a record having feild name , email , salary
>> > >
>> > > r.name is 'foo'
>> > > r.email is 'bar@foo.com'
>> > > r.salary is 1000
>> > >
>> > > suppose feild_name iterates via a FOR LOOP
>> > > through values (name , email , salary)
>> > >
>> > > is it possible to access that feild in record r
>> > > inside the loop ?
>> >
>> > Not that I'm aware of. What you can do it build the query to SELECT <variable> FROM table.
>> >
>> > Another alternative is to switch languages. A perl, tcl, etc. based function would be
>> > capable of doing what you want.
> --
> Rod Taylor <rbt@rbt.ca>
>
> PGP Key: http://www.rbt.ca/rbtpub.asc
-----------------------------------------
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: plpgsql question
@ 2003-11-08 23:46 Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 14+ messages in thread
From: Josh Berkus @ 2003-11-08 23:46 UTC (permalink / raw)
To: Rich Seiersen <rich67dev@hotmail.com>; +Cc: pgsql-sql
Rich,
> I don't want to abuse you - so if this is the wrong forum for this, feel
> free to toss it back with direction :-)
No, but I do think that you should CC a mailing list becuase:
1) Someone else may have direct experience with your problem and answer more
quickly than me, and:
2) Other people will want to read your issue and share in the solution.
Therefore I've cc'd the SQL list. Hope that's OK. I've omitted the full text
of your procedure in case it contains proprietary info.
> I think I might be running up against an error with plpgsql in temrs of seg
> faults with null values in stored procs. Here is my stored proc, the
> error, and the datatype definintion:
> 1. I am using 7.3 btw
> 2. WARNING: Error occurred while executing PL/pgSQL function
> getcaseaccounttransactionlist
> WARNING: line 44 at return next
> ERROR: Wrong record type supplied in RETURN NEXT
Um, that's a regular error. How is it a Seg Fault?
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: plpgsql question
@ 2003-11-09 00:14 Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 14+ messages in thread
From: Josh Berkus @ 2003-11-09 00:14 UTC (permalink / raw)
To: Rich Seiersen <rich67dev@hotmail.com>; +Cc: pgsql-sql
Rich,
> That was my thought - I was fairly sure I fixed it though..... Do, really
> need to be specific in terms of type in terms of int, or can I put numeric,
INT and numeric are fairly different, and I believe that SRF return types are
very fussy about data types; I wouldn't be surprised if you got an error for
using an INT4 in place of an INT8.
> and for varchar(30) I can just put varchar in my type definition.
That I don't think will be a problem; varchar limits are indifferently
supported anyway.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 14+ messages in thread
* plpgsql question
@ 2005-08-30 16:15 Postgres Admin <postgres@productivitymedia.com>
0 siblings, 1 reply; 14+ messages in thread
From: Postgres Admin @ 2005-08-30 16:15 UTC (permalink / raw)
To: pgsql-sql; +Cc: PostgreSQL - Admin <pgsql-admin@postgresql.org>
Can I do something like this:
CREATE TABLE sample (id SERIAL, node INTEGER, parent INTEGER);
INSERT INTO sample(node,parent) VALUES(1,0);
INSERT INTO sample(node,parent) VALUES(2,0);
INSERT INTO sample(node,parent) VALUES(3,1);
INSERT INTO sample(node,parent) VALUES(4,3)
CREATE OR REPLACE FUNCTION article_display(anyelement, anyelement)
RETURNS SETOF samle AS $$
DECLARE
articleRow sample%ROWTYPE;
BEGIN
FOR articleRow IN SELECT comments
FROM theirry.articles
ORDER BY article_id
DESC LIMIT $1
OFFSET $2 LOOP
RETURN NEXT articleRow;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
Thanks,
J
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: plpgsql question
@ 2005-08-30 16:50 Michael Fuhr <mike@fuhr.org>
parent: Postgres Admin <postgres@productivitymedia.com>
0 siblings, 1 reply; 14+ messages in thread
From: Michael Fuhr @ 2005-08-30 16:50 UTC (permalink / raw)
To: Postgres Admin <postgres@productivitymedia.com>; +Cc: pgsql-sql@postgresql.org, PostgreSQL - Admin <pgsql-admin@postgresql.org>
On Tue, Aug 30, 2005 at 12:15:54PM -0400, Postgres Admin wrote:
>
> Can I do something like this:
It's good that you gave an example, but it would also be good to
give a summary of what you're trying to do and what trouble you're
having so people don't have to guess.
> CREATE TABLE sample (id SERIAL, node INTEGER, parent INTEGER);
> INSERT INTO sample(node,parent) VALUES(1,0);
> INSERT INTO sample(node,parent) VALUES(2,0);
> INSERT INTO sample(node,parent) VALUES(3,1);
> INSERT INTO sample(node,parent) VALUES(4,3)
>
> CREATE OR REPLACE FUNCTION article_display(anyelement, anyelement)
> RETURNS SETOF samle AS $$
I assume you want to return "SETOF sample", not "samle". When
posting code please post the actual code you're running so typos
don't distract from the real problem (unless a typo *is* part of
the problem).
> DECLARE
> articleRow sample%ROWTYPE;
> BEGIN
> FOR articleRow IN SELECT comments
> FROM theirry.articles
You're selecting a column of one table (theirry.articles) into a
row type variable of another table (sample). If the value of
comments can't be converted to an integer (the type of sample's
first column) then you'll get a syntax error. And what about the
other columns of sample? What are you really trying to do here?
> ORDER BY article_id
> DESC LIMIT $1
> OFFSET $2 LOOP
If you're using the function's arguments like this, why did you
declare them as anyelement instead of integer?
> RETURN NEXT articleRow;
> END LOOP;
> RETURN;
> END;
> $$ LANGUAGE plpgsql;
It's not clear what you're trying to do nor what problems you're
having. Is this example real or a contrived facsimile of what
you're really trying to do? Could you provide some more information?
--
Michael Fuhr
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [SQL] plpgsql question
@ 2005-08-30 17:59 Postgres Admin <postgres@productivitymedia.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 14+ messages in thread
From: Postgres Admin @ 2005-08-30 17:59 UTC (permalink / raw)
To: ; +Cc: pgsql-sql@postgresql.org, PostgreSQL - Admin <pgsql-admin@postgresql.org>
I have data in one table called articles and I would like to make a
function in which takes certain data from it and display the results.
Example:
CREATE TABLE articles (
article_id serial,
title varchar(200),
posted timestamp,
article_subject varchar(200),
article_body text,
allow_comments boolean,
comments smallint
);
I understand one way to display a results I would like is creating a
TYPE with the columns needed.
CREATE TYPE articles_output AS (
article_id int
title varchar(200),
article_body text,
comments smallint
);
Now I would like the function to display data using the LIMIT and OFFSET
option
ex: SELECT title, article_body, comments FROM articles ORDER BY
article_id DESC *LIMIT 4 OFFSET 0*;
this is function I created:
CREATE OR REPLACE FUNCTION article_display(integer, integer)
RETURNS SETOF article_output AS $$
DECLARE
articleRow article_output%ROWTYPE;
sampleRow RECORD;
BEGIN
FOR sampleRow IN SELECT title, article_body, comments
FROM articles
ORDER BY article_id
DESC LIMIT $1
OFFSET $2 LOOP
RETURN NEXT sampleRow;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
this is the error ->
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "article_sample" line 10 at return next
Can I do this.... or are there better options?
Thanks for the help,
J
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [SQL] plpgsql question
@ 2005-08-31 02:32 Michael Fuhr <mike@fuhr.org>
parent: Postgres Admin <postgres@productivitymedia.com>
0 siblings, 0 replies; 14+ messages in thread
From: Michael Fuhr @ 2005-08-31 02:32 UTC (permalink / raw)
To: Postgres Admin <postgres@productivitymedia.com>; +Cc: pgsql-sql@postgresql.org, PostgreSQL - Admin <pgsql-admin@postgresql.org>
On Tue, Aug 30, 2005 at 01:59:04PM -0400, Postgres Admin wrote:
> ERROR: set-valued function called in context that cannot accept a set
> CONTEXT: PL/pgSQL function "article_sample" line 10 at return next
You don't show what you did to get this error, but I'd guess you
called the function like this:
SELECT article_display(10, 0); -- wrong
You should have called it like this:
SELECT * FROM article_display(10, 0);
The function as posted has a few other problems, like returning the
wrong type (you return a RECORD variable instead of an article_output
variable) and not selecting enough columns for the declared return
type. The return type also doesn't match the type shown in the
CREATE TYPE statement, and the CREATE TYPE statement fails with a
syntax error due to a missing comma. As I mentioned previously,
please post the *exact* code you're running so typographic errors
in the message don't distract from the real problem. Those typos
prevent people from loading the code into their own database so
they can test it unless they correct the mistakes, and then they
can't be sure they're running the same thing you are.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 14+ messages in thread
* PLPGSQL question
@ 2006-11-02 23:06 Sorin Schwimmer <sxn02@yahoo.com>
0 siblings, 0 replies; 14+ messages in thread
From: Sorin Schwimmer @ 2006-11-02 23:06 UTC (permalink / raw)
To: pgsql-sql
Hi All,
I have a stored procedure that fails and don't know how to fix it, so I hope to find some help here.
I works on a table called 'locations' that looks like this:
design=# \d locations
Table "public.locations"
Column | Type | Modifiers
---------+----------------------+---------------------
store | character(1) | not null
coord | character varying(8) | not null
room | real | not null
mu | smallint | not null
block | boolean | default false
tags | bit varying(100) | default B'0'::"bit"
details | text |
Indexes:
"locations_pkey" PRIMARY KEY, btree (store, coord)
"store_coord_locations" btree (store, coord)
Check constraints:
"locations_room_check" CHECK (room >= 0.0::double precision)
Foreign-key constraints:
"locations_store_fkey" FOREIGN KEY (store) REFERENCES stores(code)
After some work, it tries to execute the following update:
UPDATE locations SET tags=SUBSTRING(bv,1,r),room=room-$3 WHERE coord=$2 AND store=$1;
and the variables, shown by
raise notice '% % % % % %',bv,r,$3,$2,$1,data.room;
look like this:
NOTICE: 1110110000111000000...0 13 4 BK1B ÷ 4.58
The first value is a binary string, 96 bits long, the tail are just zeroes, so I cut it.
The initial values in the table are:
design=# select * from locations where coord='BK1B' and store=chr(247);
store | coord | room | mu | block | tags | details
-------+-------+------+----+-------+---------------+---------
÷ | BK1B | 4.58 | 1 | f | 1010110000111 | D
(1 row)
And now the error:
ProgrammingError: ERROR: new row for relation "locations" violates check constraint
"location_room_check"
and then the context
If I try to make the update manualy, works flawless (as 4.58-4 >= 0.0).
For now I droped the constrained, but how can I solve it?
Thanks,
Sorin
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2006-11-02 23:06 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-28 12:12 plpgsql Question.. Rajesh Kumar Mallah <mallah@trade-india.com>
2003-04-28 13:00 ` Rod Taylor <rbt@rbt.ca>
2003-04-28 13:23 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-04-28 13:31 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-04-28 13:46 ` Rod Taylor <rbt@rbt.ca>
2003-04-28 15:05 ` Rajesh Kumar Mallah <rmallah@trade-india.com>
2003-04-29 00:14 ` mallah@trade-india.com
2003-11-08 23:46 Re: plpgsql question Josh Berkus <josh@agliodbs.com>
2003-11-09 00:14 Re: plpgsql question Josh Berkus <josh@agliodbs.com>
2005-08-30 16:15 plpgsql question Postgres Admin <postgres@productivitymedia.com>
2005-08-30 16:50 ` Re: plpgsql question Michael Fuhr <mike@fuhr.org>
2005-08-30 17:59 ` Re: [SQL] plpgsql question Postgres Admin <postgres@productivitymedia.com>
2005-08-31 02:32 ` Re: [SQL] plpgsql question Michael Fuhr <mike@fuhr.org>
2006-11-02 23:06 PLPGSQL question Sorin Schwimmer <sxn02@yahoo.com>
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