Re: [SQL] function returning result set of varying column
Hi Maria,
Try something like
CREATE OR REPLACE FUNCTION myfunction() RETURNS SETOF RECORD AS
$body$
DECLARE
rec record;
BEGIN
FOR rec IN (
SELECT * FROM sometable)
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE;
As you can see, the number and type of the output fields only depends on
whatever table you query in the FOR loop.
It's not magic though. It just postpones defining the number and type of the
output fields until querying the function.
You will have to define the output fields when querying your function, like
select * from myfunction() as ("field1" integer, "field2" text, ...)
>>> "maria s" <[EMAIL PROTECTED]> 2008-06-02 22:40 >>>
Hi friends,
I am very new to plsql.
I have to write a function that quries few tables and returns a resultset of
varying column.
In that case I cannot predefine the table with column.
If I use RETURNS SETOF then I should know the number of columns and its type?!
Is there anyway to return a resultset with any number of column?
Thanks for your help.
-maria
Re: [SQL] function returning result set of varying column
Hi Friends,
Thanks for all your for the reply.
I tried the function and when I execute it using
select * from myfunction()
it says
ERROR: a column definition list is required for functions returning
"record"
Could you please help me to fix this error?
Thanks so much for your help.
-maria
On Tue, Jun 3, 2008 at 3:06 AM, Bart Degryse <[EMAIL PROTECTED]>
wrote:
> Hi Maria,
> Try something like
> CREATE OR REPLACE FUNCTION myfunction() RETURNS SETOF RECORD AS
> $body$
> DECLARE
> rec record;
> BEGIN
> FOR rec IN (
> SELECT * FROM sometable)
> LOOP
> RETURN NEXT rec;
> END LOOP;
> RETURN;
> END;
> $body$
> LANGUAGE 'plpgsql' VOLATILE;
>
> As you can see, the number and type of the output fields only depends on
> whatever table you query in the FOR loop.
> It's not magic though. It just postpones defining the number and type of
> the output fields until querying the function.
> You will have to define the output fields when querying your function, like
> select * from myfunction() as ("field1" integer, "field2" text, ...)
>
> >>> "maria s" <[EMAIL PROTECTED]> 2008-06-02 22:40 >>>
>
> Hi friends,
> I am very new to plsql.
>
> I have to write a function that quries few tables and returns a resultset
> of varying column.
>
> In that case I cannot predefine the table with column.
> If I use RETURNS SETOF then I should know the number of columns and its
> type?!
>
> Is there anyway to return a resultset with any number of column?
>
> Thanks for your help.
>
> -maria
>
Re: [SQL] function returning result set of varying column
As I wrote before you will have to define your fields when querying the
function,
eg. select * from myfunction() as ("field1" integer, "field2" text, ...)
So suppose you have a table like this
CREATE TABLE sometable (
"id" serial,
"sometextfield" text,
"aninteger" int,
"andavarchar" varchar(20)
);
you would have to query the function below like this:
select * from myfunction() as ("id" integer, "sometextfield" text, "aninteger"
integer, "andavarchar" varchar);
You are however not obliged to use the same fieldnames as defined in the table
definition, so this will work too:
select * from myfunction() as ("myid" integer, "textfield" text, "myint"
integer, "varfield" varchar);
Automatic type conversion will work too, so this is ok too
select * from myfunction() as ("myid" integer, "textfield" text, "myint"
integer, "varfield" text);
Now suppose you change the selecte statement in myfunction to
SELECT sometextfield, andavarchar FROM sometable WHERE id > 5
Now myfunction will not return 4 fields but only 2 and thus you would query
myfunction like this
select * from myfunction() as ("sometextfield" text, "andavarchar" varchar);
or
select * from myfunction() as ("textfield" text, "varfield" varchar);
or
select * from myfunction() as ("textfield" text, "varfield" text);
So defining your function as RETURNS SETOF RECORD only postpones the moment you
define your output fields.
Either you define your output fields when creating your function, or (like in
the above example) when querying your function.
>>> "maria s" <[EMAIL PROTECTED]> 2008-06-03 15:12 >>>
Hi Bart,
I will not know the number of fields. Because it will vary .
Is there any solution for this?
Thanks,
Maria
>>> "maria s" <[EMAIL PROTECTED]> 2008-06-03 15:01 >>>
Hi Friends,
Thanks for all your for the reply.
I tried the function and when I execute it using
select * from myfunction()
it says
ERROR: a column definition list is required for functions returning "record"
Could you please help me to fix this error?
Thanks so much for your help.
-maria
On Tue, Jun 3, 2008 at 3:06 AM, Bart Degryse <[EMAIL PROTECTED]> wrote:
Hi Maria,
Try something like
CREATE OR REPLACE FUNCTION myfunction() RETURNS SETOF RECORD AS
$body$
DECLARE
rec record;
BEGIN
FOR rec IN (
SELECT * FROM sometable)
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE;
As you can see, the number and type of the output fields only depends on
whatever table you query in the FOR loop.
It's not magic though. It just postpones defining the number and type of the
output fields until querying the function.
You will have to define the output fields when querying your function, like
select * from myfunction() as ("field1" integer, "field2" text, ...)
>>> "maria s" <[EMAIL PROTECTED]> 2008-06-02 22:40 >>>
Hi friends,
I am very new to plsql.
I have to write a function that quries few tables and returns a resultset of
varying column.
In that case I cannot predefine the table with column.
If I use RETURNS SETOF then I should know the number of columns and its type?!
Is there anyway to return a resultset with any number of column?
Thanks for your help.
-maria
Re: [SQL] function returning result set of varying column
On Tue, 3 Jun 2008 09:01:02 -0400 "maria s" <[EMAIL PROTECTED]> wrote: > Hi Friends, > Thanks for all your for the reply. > > I tried the function and when I execute it using > select * from myfunction() > it says > ERROR: a column definition list is required for functions > returning "record" > > Could you please help me to fix this error? > > Thanks so much for your help. you can specify the returned types in each statement that call your function or you can specify the returned type in the function itself. CREATE OR REPLACE FUNCTION myfunction(out col1 int, out col2 varchar(32), out ...) RETURNS SETOF RECORD AS $body$ DECLARE rec record; BEGIN FOR rec IN ( SELECT * FROM sometable) LOOP col1:=rec.col1; col2:=rec.col2; --col3:=...; RETURN NEXT; END LOOP; RETURN; END; $body$ > > CREATE OR REPLACE FUNCTION myfunction() RETURNS SETOF RECORD AS > > $body$ > > DECLARE > > rec record; > > BEGIN > > FOR rec IN ( > > SELECT * FROM sometable) > > LOOP > > RETURN NEXT rec; > > END LOOP; > > RETURN; > > END; > > $body$ -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] function returning result set of varying column
Thanks for all your replies. Actually I don't know the number of columns that I am going to return. I have 2 tables. For a single entry E1 in one table(t1), I have to fetch all the matching entries for E1 from the other table(t2), K1,..Kn. and finally the function should return E1, K1..Kn. So I don't know the number of columns that I am going to get. Is it possible to write a function that returns this kind of result? Please help. Thanks, maria On Tue, Jun 3, 2008 at 9:28 AM, Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: > On Tue, 3 Jun 2008 09:01:02 -0400 > "maria s" <[EMAIL PROTECTED]> wrote: > > > Hi Friends, > > Thanks for all your for the reply. > > > > I tried the function and when I execute it using > > select * from myfunction() > > it says > > ERROR: a column definition list is required for functions > > returning "record" > > > > Could you please help me to fix this error? > > > > Thanks so much for your help. > > you can specify the returned types in each statement that call your > function or you can specify the returned type in the function itself. > > CREATE OR REPLACE FUNCTION myfunction(out col1 int, out col2 > varchar(32), out ...) > RETURNS > SETOF > RECORD > AS > $body$ > DECLARE > rec record; > BEGIN > FOR rec IN ( >SELECT * FROM sometable) > LOOP > col1:=rec.col1; >col2:=rec.col2; > --col3:=...; >RETURN NEXT; > END LOOP; > RETURN; > END; > $body$ > > > > CREATE OR REPLACE FUNCTION myfunction() RETURNS SETOF RECORD AS > > > $body$ > > > DECLARE > > > rec record; > > > BEGIN > > > FOR rec IN ( > > > SELECT * FROM sometable) > > > LOOP > > > RETURN NEXT rec; > > > END LOOP; > > > RETURN; > > > END; > > > $body$ > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] cross-database references are not implemented
Hello
it works for me
postgres=# create schema export;
CREATE SCHEMA
Time: 45,918 ms
postgres=# create table public.a(a varchar);
CREATE TABLE
Time: 91,385 ms
postgres=# create table export.a(a varchar);
\CREATE TABLE
Time: 9,462 ms
postgres=# create function ftrg() returns trigger as $$begin insert
into export.a values(new.*); return new; end$$ language plpgsql;
CREATE FUNCTION
Time: 486,395 ms
postgres=# \h CREATE trigger
Command: CREATE TRIGGER
Description: define a new trigger
Syntax:
CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE funcname ( arguments )
postgres=# CREATE TRIGGER aaa after insert on public.a for each row
execute procedure ftrg();
CREATE TRIGGER
Time: 5,848 ms
postgres=# insert into public.a values('ahoj');
INSERT 0 1
Time: 5,179 ms
postgres=# SELECT * from export.a ;
a
--
ahoj
(1 row)
postgresql 8.3
Pavel
2008/6/3 Paul Dam <[EMAIL PROTECTED]>:
> Hoi,
>
>
>
> I have a database with 2 schemas:
>
> - public
>
> - export
>
>
>
> In the export schema I have tables that are filled during an export process.
>
> There is some data I want to have in a table in the public schema as well.
>
> I wrote a trigger function that after insert in the export table does an
> export in the public table.
>
>
>
> If I do an insert I get the error message: "ERROR: cross-database
> references are not implemented".
>
>
>
> How can I solve this?
>
>
>
> Met vriendelijke groet,
>
>
>
> Paul Dam
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] function returning result set of varying column
On Tue, 3 Jun 2008 09:41:27 -0400 "maria s" <[EMAIL PROTECTED]> wrote: > Thanks for all your replies. > > Actually I don't know the number of columns that I am going to > return. > > I have 2 tables. For a single entry E1 in one table(t1), I have > to fetch all the matching entries for E1 from the other > table(t2), K1,..Kn. and finally the function should return E1, > K1..Kn. So I don't know the number of columns that I am going to > get. > > Is it possible to write a function that returns this kind of > result? Up to my knowledge as Bart wrote in pl/pgsql you'll have to specify somewhere the return type: in the function or in the select calling the function. If you use sql (not pl/pgsql) function you shouldn't be obliged to specify the return type. But I haven't written enough sql function to actually remember how it works. If you post your tentative sql it could give us more clue. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] cross-database references are not implemented
Hoi, I have a database with 2 schemas: - public - export In the export schema I have tables that are filled during an export process. There is some data I want to have in a table in the public schema as well. I wrote a trigger function that after insert in the export table does an export in the public table. If I do an insert I get the error message: "ERROR: cross-database references are not implemented". How can I solve this? Met vriendelijke groet, Paul Dam
Re: [SQL] cross-database references are not implemented
On Tuesday 03 June 2008 6:12 am, Paul Dam wrote: > Hoi, > > > > I have a database with 2 schemas: > > - public > > - export > > > > In the export schema I have tables that are filled during an export > process. > > There is some data I want to have in a table in the public schema as > well. > > I wrote a trigger function that after insert in the export table does an > export in the public table. > > > > If I do an insert I get the error message: "ERROR: cross-database > references are not implemented". > > > > How can I solve this? > > > > Met vriendelijke groet, > > > > Paul Dam You will need to show the actual query, but I suspect you have an extra period in your table name. Instead of public.table_name you have something like public.table.name. -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] function returning result set of varying column
Hi Ivan, If I have to know the column names then I can't use the Functions. As I said before, the columns will vary. or As Pavel Stehule said I will use arrays. Is anyone can show an example of returning a record with string and array? Thanks, Maria On Tue, Jun 3, 2008 at 9:57 AM, Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: > On Tue, 3 Jun 2008 09:41:27 -0400 > "maria s" <[EMAIL PROTECTED]> wrote: > > > Thanks for all your replies. > > > > Actually I don't know the number of columns that I am going to > > return. > > > > I have 2 tables. For a single entry E1 in one table(t1), I have > > to fetch all the matching entries for E1 from the other > > table(t2), K1,..Kn. and finally the function should return E1, > > K1..Kn. So I don't know the number of columns that I am going to > > get. > > > > Is it possible to write a function that returns this kind of > > result? > > Up to my knowledge as Bart wrote in pl/pgsql you'll have to specify > somewhere the return type: in the function or in the select calling > the function. > If you use sql (not pl/pgsql) function you shouldn't be obliged to > specify the return type. > But I haven't written enough sql function to actually remember how > it works. > > If you post your tentative sql it could give us more clue. > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] cross-database references are not implemented
Thanks Adrian, I casted a column to a type with "value::..%type" That was the problem. Met vriendelijke groet, Paul Dam -Oorspronkelijk bericht- Van: Adrian Klaver [mailto:[EMAIL PROTECTED] Verzonden: dinsdag 3 juni 2008 15:57 Aan: pgsql-sql@postgresql.org CC: Paul Dam Onderwerp: Re: [SQL] cross-database references are not implemented On Tuesday 03 June 2008 6:12 am, Paul Dam wrote: > Hoi, > > > > I have a database with 2 schemas: > > - public > > - export > > > > In the export schema I have tables that are filled during an export > process. > > There is some data I want to have in a table in the public schema as > well. > > I wrote a trigger function that after insert in the export table does an > export in the public table. > > > > If I do an insert I get the error message: "ERROR: cross-database > references are not implemented". > > > > How can I solve this? > > > > Met vriendelijke groet, > > > > Paul Dam You will need to show the actual query, but I suspect you have an extra period in your table name. Instead of public.table_name you have something like public.table.name. -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] function returning result set of varying column
2008/6/3 maria s <[EMAIL PROTECTED]>: > Thanks for all your replies. > > Actually I don't know the number of columns that I am going to return. > > I have 2 tables. For a single entry E1 in one table(t1), I have to fetch > all the matching entries for E1 from the other table(t2), K1,..Kn. > and finally the function should return E1, K1..Kn. So I don't know the > number of columns that I am going to get. > > Is it possible to write a function that returns this kind of result? no, it's not possible. You have to know number and types of result columns before function's execution. try to use arrays. Regards Pavel Stehule > > Please help. > > Thanks, > maria > > On Tue, Jun 3, 2008 at 9:28 AM, Ivan Sergio Borgonovo <[EMAIL PROTECTED]> > wrote: >> >> On Tue, 3 Jun 2008 09:01:02 -0400 >> "maria s" <[EMAIL PROTECTED]> wrote: >> >> > Hi Friends, >> > Thanks for all your for the reply. >> > >> > I tried the function and when I execute it using >> > select * from myfunction() >> > it says >> > ERROR: a column definition list is required for functions >> > returning "record" >> > >> > Could you please help me to fix this error? >> > >> > Thanks so much for your help. >> >> you can specify the returned types in each statement that call your >> function or you can specify the returned type in the function itself. >> >> CREATE OR REPLACE FUNCTION myfunction(out col1 int, out col2 >> varchar(32), out ...) >> RETURNS >> SETOF >> RECORD >> AS >> $body$ >> DECLARE >> rec record; >> BEGIN >> FOR rec IN ( >>SELECT * FROM sometable) >> LOOP >>col1:=rec.col1; >>col2:=rec.col2; >> --col3:=...; >>RETURN NEXT; >> END LOOP; >> RETURN; >> END; >> $body$ >> >> > > CREATE OR REPLACE FUNCTION myfunction() RETURNS SETOF RECORD AS >> > > $body$ >> > > DECLARE >> > > rec record; >> > > BEGIN >> > > FOR rec IN ( >> > > SELECT * FROM sometable) >> > > LOOP >> > > RETURN NEXT rec; >> > > END LOOP; >> > > RETURN; >> > > END; >> > > $body$ >> >> -- >> Ivan Sergio Borgonovo >> http://www.webthatworks.it >> >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] function returning result set of varying column
On Tue, 3 Jun 2008 10:06:45 -0400 "maria s" <[EMAIL PROTECTED]> wrote: > Hi Ivan, > If I have to know the column names then I can't use the Functions. > As I said before, the columns will vary. or As Pavel Stehule said > I will use arrays. > > Is anyone can show an example of returning a record with string > and array? What I meant was that with "pure" sql functions you shouldn't have that constraint... wrapping a select in a function create or replace function fsql() as $$ begin select * from mytable; end; $$ language sql; should work. SQL functions are less flexible than pg/plsql but maybe you can live with it. SQL functions still support conditionals etc... you'd have to see if what SQL functions offer is enough for your need. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] function returning result set of varying column
Hi Pavel Stehule,
Thanks for your reply.
If I want to return a string and an array how should I do it?
The problem is as I explained before.
I have 2 tables. For a single entry E1 in one table(t1), I have
to fetch all the matching entries for E1 from the other
table(t2), K1,..Kn, M1...Mn and finally the function should return E1,
K1..Kn, M1...Mn.
t1
sample-id,
samplename
recs
1 c-01
2 c-02
t2
sampleid, property_name, property_value
recs
1 , lps , 1
1, hr, 2
1, cd04, 1
2, lps, 1
2, hr, 5
Could you please tell me how should I get this as string and array type of
[][] that fetches propert_type and value array?
select * from myfunction() as ("field1" text, "field2" text[][])
Thanks a lot for your help,
Maria
On Tue, Jun 3, 2008 at 10:13 AM, Pavel Stehule <[EMAIL PROTECTED]>
wrote:
> 2008/6/3 maria s <[EMAIL PROTECTED]>:
> > Hi Ivan,
> > If I have to know the column names then I can't use the Functions.
> > As I said before, the columns will vary. or As Pavel Stehule said
> > I will use arrays.
> >
> > Is anyone can show an example of returning a record with string and
> array?
>
>
> postgres=# create or replace function foo(int) returns text[] as
> $$select array(select 'kuku' || i from generate_series(1,$1) g(i))$$
> language sql;
> CREATE FUNCTION
> Time: 69,730 ms
> postgres=# select foo(10);
> foo
>
> {kuku1,kuku2,kuku3,kuku4,kuku5,kuku6,kuku7,kuku8,kuku9,kuku10}
> (1 row)
>
> Time: 1,739 ms
> postgres=# select foo(5);
> foo
> -
> {kuku1,kuku2,kuku3,kuku4,kuku5}
> (1 row)
>
> Time: 1,274 ms
>
> >
> > Thanks,
> > Maria
> >
> > On Tue, Jun 3, 2008 at 9:57 AM, Ivan Sergio Borgonovo <
> [EMAIL PROTECTED]>
> > wrote:
> >>
> >> On Tue, 3 Jun 2008 09:41:27 -0400
> >> "maria s" <[EMAIL PROTECTED]> wrote:
> >>
> >> > Thanks for all your replies.
> >> >
> >> > Actually I don't know the number of columns that I am going to
> >> > return.
> >> >
> >> > I have 2 tables. For a single entry E1 in one table(t1), I have
> >> > to fetch all the matching entries for E1 from the other
> >> > table(t2), K1,..Kn. and finally the function should return E1,
> >> > K1..Kn. So I don't know the number of columns that I am going to
> >> > get.
> >> >
> >> > Is it possible to write a function that returns this kind of
> >> > result?
> >>
> >> Up to my knowledge as Bart wrote in pl/pgsql you'll have to specify
> >> somewhere the return type: in the function or in the select calling
> >> the function.
> >> If you use sql (not pl/pgsql) function you shouldn't be obliged to
> >> specify the return type.
> >> But I haven't written enough sql function to actually remember how
> >> it works.
> >>
> >> If you post your tentative sql it could give us more clue.
> >>
> >> --
> >> Ivan Sergio Borgonovo
> >> http://www.webthatworks.it
> >>
> >>
> >> --
> >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-sql
> >
> >
>
Re: [SQL] function returning result set of varying column
2008/6/3 maria s <[EMAIL PROTECTED]>:
> Hi Ivan,
> If I have to know the column names then I can't use the Functions.
> As I said before, the columns will vary. or As Pavel Stehule said
> I will use arrays.
>
> Is anyone can show an example of returning a record with string and array?
postgres=# create or replace function foo(int) returns text[] as
$$select array(select 'kuku' || i from generate_series(1,$1) g(i))$$
language sql;
CREATE FUNCTION
Time: 69,730 ms
postgres=# select foo(10);
foo
{kuku1,kuku2,kuku3,kuku4,kuku5,kuku6,kuku7,kuku8,kuku9,kuku10}
(1 row)
Time: 1,739 ms
postgres=# select foo(5);
foo
-
{kuku1,kuku2,kuku3,kuku4,kuku5}
(1 row)
Time: 1,274 ms
>
> Thanks,
> Maria
>
> On Tue, Jun 3, 2008 at 9:57 AM, Ivan Sergio Borgonovo <[EMAIL PROTECTED]>
> wrote:
>>
>> On Tue, 3 Jun 2008 09:41:27 -0400
>> "maria s" <[EMAIL PROTECTED]> wrote:
>>
>> > Thanks for all your replies.
>> >
>> > Actually I don't know the number of columns that I am going to
>> > return.
>> >
>> > I have 2 tables. For a single entry E1 in one table(t1), I have
>> > to fetch all the matching entries for E1 from the other
>> > table(t2), K1,..Kn. and finally the function should return E1,
>> > K1..Kn. So I don't know the number of columns that I am going to
>> > get.
>> >
>> > Is it possible to write a function that returns this kind of
>> > result?
>>
>> Up to my knowledge as Bart wrote in pl/pgsql you'll have to specify
>> somewhere the return type: in the function or in the select calling
>> the function.
>> If you use sql (not pl/pgsql) function you shouldn't be obliged to
>> specify the return type.
>> But I haven't written enough sql function to actually remember how
>> it works.
>>
>> If you post your tentative sql it could give us more clue.
>>
>> --
>> Ivan Sergio Borgonovo
>> http://www.webthatworks.it
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] function returning result set of varying column
2008/6/3 maria s <[EMAIL PROTECTED]>:
> Hi Pavel Stehule,
> Thanks for your reply.
>
> If I want to return a string and an array how should I do it?
> The problem is as I explained before.
postgres=# create or replace function foo(j integer, out a varchar,
out b varchar[]) as $$
begin a := 'kuku'; b := '{}';
for i in 1..j loop b := b || (a || i)::varchar; end loop;
end; $$ language plpgsql;
CREATE FUNCTION
Time: 4,819 ms
postgres=# select * from foo(3);
a | b
--+-
kuku | {kuku1,kuku2,kuku3}
(1 row)
>
> I have 2 tables. For a single entry E1 in one table(t1), I have
>to fetch all the matching entries for E1 from the other
>table(t2), K1,..Kn, M1...Mn and finally the function should return E1,
>K1..Kn, M1...Mn.
postgres=# create table a(x integer);
CREATE TABLE
Time: 140,440 ms
postgres=# create table b(x integer, y integer);
CREATE TABLE
Time: 7,532 ms
postgres=# insert into a values(10),(20);
INSERT 0 2
Time: 4,065 ms
postgres=# insert into b values(10,1),(10,2),(10,3),(20,8),(20,7);
INSERT 0 5
Time: 2,711 ms
postgres=# select x, (select array(select y from b where b.x = a.x)) from a;
x | ?column?
+--
10 | {1,2,3}
20 | {8,7}
(2 rows)
other solution is using custom agg function
CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);
http://www.postgresql.org/docs/8.3/interactive/xaggr.html
postgres=# select x, array_accum(y) from b group by x;
x | array_accum
+-
20 | {8,7}
10 | {1,2,3}
(2 rows)
maybe you don't need function and you need only view. I don't know:
create view someview as select x, array_accum(y) from b group by x;
postgres=# select * from someview ;
x | array_accum
+-
20 | {8,7}
10 | {1,2,3}
(2 rows)
postgres=# create or replace function fx(integer, out varchar, out
varchar) as $$select x::varchar, (select array(select y from b where
b.x = a.x))::varchar
from a where a.x = $1
$$ language sql;
CREATE FUNCTION
Time: 5,111 ms
postgres=# select * from fx(10);
column1 | column2
-+-
10 | {1,2,3}
(1 row)
Regards
Pavel Stehule
>
> t1
> sample-id,
> samplename
>
> recs
> 1 c-01
> 2 c-02
>
> t2
> sampleid, property_name, property_value
>
> recs
> 1 , lps , 1
> 1, hr, 2
> 1, cd04, 1
>
> 2, lps, 1
> 2, hr, 5
>
>
> Could you please tell me how should I get this as string and array type of
> [][] that fetches propert_type and value array?
>
> select * from myfunction() as ("field1" text, "field2" text[][])
>
> Thanks a lot for your help,
> Maria
>
> On Tue, Jun 3, 2008 at 10:13 AM, Pavel Stehule <[EMAIL PROTECTED]>
> wrote:
>>
>> 2008/6/3 maria s <[EMAIL PROTECTED]>:
>> > Hi Ivan,
>> > If I have to know the column names then I can't use the Functions.
>> > As I said before, the columns will vary. or As Pavel Stehule said
>> > I will use arrays.
>> >
>> > Is anyone can show an example of returning a record with string and
>> > array?
>>
>>
>> postgres=# create or replace function foo(int) returns text[] as
>> $$select array(select 'kuku' || i from generate_series(1,$1) g(i))$$
>> language sql;
>> CREATE FUNCTION
>> Time: 69,730 ms
>> postgres=# select foo(10);
>> foo
>>
>> {kuku1,kuku2,kuku3,kuku4,kuku5,kuku6,kuku7,kuku8,kuku9,kuku10}
>> (1 row)
>>
>> Time: 1,739 ms
>> postgres=# select foo(5);
>> foo
>> -
>> {kuku1,kuku2,kuku3,kuku4,kuku5}
>> (1 row)
>>
>> Time: 1,274 ms
>>
>> >
>> > Thanks,
>> > Maria
>> >
>> > On Tue, Jun 3, 2008 at 9:57 AM, Ivan Sergio Borgonovo
>> > <[EMAIL PROTECTED]>
>> > wrote:
>> >>
>> >> On Tue, 3 Jun 2008 09:41:27 -0400
>> >> "maria s" <[EMAIL PROTECTED]> wrote:
>> >>
>> >> > Thanks for all your replies.
>> >> >
>> >> > Actually I don't know the number of columns that I am going to
>> >> > return.
>> >> >
>> >> > I have 2 tables. For a single entry E1 in one table(t1), I have
>> >> > to fetch all the matching entries for E1 from the other
>> >> > table(t2), K1,..Kn. and finally the function should return E1,
>> >> > K1..Kn. So I don't know the number of columns that I am going to
>> >> > get.
>> >> >
>> >> > Is it possible to write a function that returns this kind of
>> >> > result?
>> >>
>> >> Up to my knowledge as Bart wrote in pl/pgsql you'll have to specify
>> >> somewhere the return type: in the function or in the select calling
>> >> the function.
>> >> If you use sql (not pl/pgsql) function you shouldn't be obliged to
>> >> specify the return type.
>> >> But I haven't written enough sql function to actually remember how
>> >> it works.
>> >>
>> >> If you post your tentative sql it could give us more clue.
>> >>
>> >> --
>> >> Ivan Sergio Borgonovo
>> >> http://www.webthatworks.it
>> >>
>> >>
>> >> --
>> >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> >> To make changes to your subscription:
>> >> http:/
[SQL] Update problem
Hi Iam facing a strange issue One of the functions in my program is running an update statement. The statement is running cross-schema. What i mean is that the program resides in one schema where as it updates a table from another schema. How ever these scehmas are on the same database. The program runs correctly and also prints out the update statement. But it never actually updates the table.neither does it fail. However when i run one of the update statements individually in the query tool...the update happens. Iam assuming that this is not a problem with the permissions either since the permission for the table to be updated is set to public. iam pasting the update statement for reference: EXECUTE 'UPDATE '||thepartition||' SET volume='||updated_volume||',occupancy='||updated_occ||',speed='||updated_speed||' WHERE lane_id='||lane||' and measurement_start = '''||measurement_start||; Thanks Sam -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Update problem
I tried getting the output of the execute statements by printing the FOUND variable. It is returning the value as false. However i used PEFORM instead of EXECUTE for the update statement. It On 6/3/08, samantha mahindrakar <[EMAIL PROTECTED]> wrote: > Hi > Iam facing a strange issue > One of the functions in my program is running an update statement. The > statement is running cross-schema. What i mean is that the program > resides in one schema where as it updates a table from another schema. > How ever these scehmas are on the same database. > The program runs correctly and also prints out the update statement. > But it never actually updates the table.neither does it fail. > However when i run one of the update statements individually in the > query tool...the update happens. > Iam assuming that this is not a problem with the permissions either > since the permission for the table to be updated is set to public. > iam pasting the update statement for reference: > > EXECUTE 'UPDATE '||thepartition||' SET > volume='||updated_volume||',occupancy='||updated_occ||',speed='||updated_speed||' > WHERE lane_id='||lane||' and measurement_start = > '''||measurement_start||; > > > Thanks > Sam > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Update problem
Iam sorry for the previous mail..it was in complete. Please do not
consider it.
I think i could figure out the problem for the updates not happening.
Following is the function that does the update :
BEGIN
IF flag=1 THEN
tempQuery:='UPDATE '||thepartition||' SET
volume='||updated_volume||',occupancy='||updated_occ||',speed='||updated_speed||'
WHERE lane_id='||lane||' and measurement_start >=
date_trunc(''minute'',timestamp'''||measurement_start||''') AND
measurement_start <
date_trunc(''minute'',timestamp'''||measurement_start + interval '1
minute'||''')';
EXECUTE 'UPDATE '||thepartition||' SET
volume='||updated_volume||',occupancy='||updated_occ||',speed='||updated_speed||'
WHERE lane_id='||lane||' and measurement_start >=
date_trunc(''minute'',timestamp'''||measurement_start||''') AND
measurement_start <
date_trunc(''minute'',timestamp'''||measurement_start + interval '1
minute'||''')';
RAISE NOTICE 'UPDATE QUERY %',tempQuery;
RAISE NOTICE 'FOUND %',FOUND;
ELSE
tempQuery:='UPDATE '||thepartition||' SET
volume='||updated_volume||',occupancy='||updated_occ||',speed='||updated_speed||'
WHERE lane_id='||lane||' and measurement_start =
'''||measurement_start||;
EXECUTE 'UPDATE '||thepartition||' SET
volume='||updated_volume||',occupancy='||updated_occ||',speed='||updated_speed||'
WHERE lane_id='||lane||' and measurement_start =
'''||measurement_start||;
RAISE NOTICE 'UPDATE QUERY %',tempQuery;
RAISE NOTICE 'FOUND %',FOUND;
END IF;
--Update the detector health data
EXECUTE 'INSERT into lane_detector_health (lane_id,
measurement_start,detector_status)
values('||lane||','''||measurement_start||''','||health||')';
EXCEPTION WHEN integrity_constraint_violation THEN
RAISE NOTICE 'Imputation for lane % at time % has been already
imputed',lane,measurement_start;
END;
There is an insert staement at the end that has an exception block. I
think whenever an exception is getting caught the data is getting
rolled back and hence the updates are getting rolled back.
I wrote the exception thinking that the roll back happens for only one
insert statement if it happensi dont know if i have judged the
scope of the exception block wrong...
Could someone please clear me on this.
Thanks
Sam
On 6/3/08, samantha mahindrakar <[EMAIL PROTECTED]> wrote:
> I tried getting the output of the execute statements by printing the
> FOUND variable. It is returning the value as false.
> However i used PEFORM instead of EXECUTE for the update statement. It
>
> On 6/3/08, samantha mahindrakar <[EMAIL PROTECTED]> wrote:
> > Hi
> > Iam facing a strange issue
> > One of the functions in my program is running an update statement. The
> > statement is running cross-schema. What i mean is that the program
> > resides in one schema where as it updates a table from another schema.
> > How ever these scehmas are on the same database.
> > The program runs correctly and also prints out the update statement.
> > But it never actually updates the table.neither does it fail.
> > However when i run one of the update statements individually in the
> > query tool...the update happens.
> > Iam assuming that this is not a problem with the permissions either
> > since the permission for the table to be updated is set to public.
> > iam pasting the update statement for reference:
> >
> > EXECUTE 'UPDATE '||thepartition||' SET
> > volume='||updated_volume||',occupancy='||updated_occ||',speed='||updated_speed||'
> > WHERE lane_id='||lane||' and measurement_start =
> > '''||measurement_start||;
> >
> >
> > Thanks
> > Sam
> >
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
