Re: [GENERAL] Isolating a record column from a PL-Pgsql function call ?
Gauthier, Dave schrieb: > Hi: > > > > I have a PL-Pgsql function that returns a record of a type that have 4 > elements > (columns). Let’s say the first element/column is called “id_num”. Is there a > way I can specify the id_num element of the record returned from iside a > query? > > > > For example, select col1 from thetable where my_plpgsql_fn(col2).id_num = 123; I think, you should rewrite your function to hand over the id_num as additional parameter. But i'm not sure if i understand you corrently. Is this a setof-returning function? Please, show us your real function, maybe someone can help you more. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Isolating a record column from a PL-Pgsql function call ?
The example I gave was overly simplistic. I actually want to look at more than one of the columns in the returned record, so rewritting it to return an int won't address the need for the other columns. And no, it does not return a set/array of records. It returns just one record. If I run the function outside of a query, it returns something like... "(myvcharval1,myvcharval2,myintval)" Notice the parens, the commas and the lack of single quotes around the "myvcharval(x)" values. I suppose I could do some funky string dissection with this, but I was wondering if there was something more straignt-forward. -dave -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andreas Kretschmer Sent: Tuesday, December 16, 2008 2:51 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Isolating a record column from a PL-Pgsql function call ? Gauthier, Dave schrieb: > Hi: > > > > I have a PL-Pgsql function that returns a record of a type that have 4 > elements > (columns). Let's say the first element/column is called "id_num". Is there a > way I can specify the id_num element of the record returned from iside a > query? > > > > For example, select col1 from thetable where my_plpgsql_fn(col2).id_num = 123; I think, you should rewrite your function to hand over the id_num as additional parameter. But i'm not sure if i understand you corrently. Is this a setof-returning function? Please, show us your real function, maybe someone can help you more. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Isolating a record column from a PL-Pgsql function call ?
Gauthier, Dave schrieb: > Hi: > > > > I have a PL-Pgsql function that returns a record of a type that have 4 > elements > (columns). Let’s say the first element/column is called “id_num”. Is there a > way I can specify the id_num element of the record returned from iside a > query? > > > > For example, select col1 from thetable where my_plpgsql_fn(col2).id_num = 123; Okay, i think, i got it: test=# create function x(in int, out a int, out b int) returns record as $$begin a:=1;b:=2;return; end; $$language plpgsql; CREATE FUNCTION Time: 0.412 ms test=*# select 5 where 3=(select b from x(2)); ?column? -- (0 rows) Time: 0.335 ms test=*# select 5 where 2=(select b from x(2)); ?column? -- 5 (1 row) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Isolating a record column from a PL-Pgsql function call ?
And it's a bit more complicated at that. Here's why I discovered that I can refer to the function as a relation in the from clause... select y.col1 from my_plpgsql_fn(789) y but I need to pass the function something from outside, sort of like... select x.col_a, y.col1 from mytable x, my_plpgsql_fn(a.val) y where x.col_b = 'foo'; When I try this one, I get a "ERROR Function expression in FROM cannot refer to other relations of same query level" I could accomplish what I need to because Im actually doing all this in the Perl/DBI. And in Perl, I can just run the "x" query in a loop, then the "my_plpgsql_fn" call inside the loop. This will accomplish the same thing, but it'll be 2 queries instead of one (and it'll be far from elegant!) -dave -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Gauthier, Dave Sent: Tuesday, December 16, 2008 3:03 PM To: Andreas Kretschmer; pgsql-general@postgresql.org Subject: Re: [GENERAL] Isolating a record column from a PL-Pgsql function call ? The example I gave was overly simplistic. I actually want to look at more than one of the columns in the returned record, so rewritting it to return an int won't address the need for the other columns. And no, it does not return a set/array of records. It returns just one record. If I run the function outside of a query, it returns something like... "(myvcharval1,myvcharval2,myintval)" Notice the parens, the commas and the lack of single quotes around the "myvcharval(x)" values. I suppose I could do some funky string dissection with this, but I was wondering if there was something more straignt-forward. -dave -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andreas Kretschmer Sent: Tuesday, December 16, 2008 2:51 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Isolating a record column from a PL-Pgsql function call ? Gauthier, Dave schrieb: > Hi: > > > > I have a PL-Pgsql function that returns a record of a type that have 4 > elements > (columns). Let's say the first element/column is called "id_num". Is there a > way I can specify the id_num element of the record returned from iside a > query? > > > > For example, select col1 from thetable where my_plpgsql_fn(col2).id_num = 123; I think, you should rewrite your function to hand over the id_num as additional parameter. But i'm not sure if i understand you corrently. Is this a setof-returning function? Please, show us your real function, maybe someone can help you more. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Isolating a record column from a PL-Pgsql function call ?
Wooops... typo... should have been select x.col_a, y.col1 from mytable x, my_plpgsql_fn(x.val) y where x.col_b = 'foo'; -Original Message- From: Gauthier, Dave Sent: Tuesday, December 16, 2008 3:38 PM To: Gauthier, Dave; Andreas Kretschmer; pgsql-general@postgresql.org Subject: RE: [GENERAL] Isolating a record column from a PL-Pgsql function call ? And it's a bit more complicated at that. Here's why I discovered that I can refer to the function as a relation in the from clause... select y.col1 from my_plpgsql_fn(789) y but I need to pass the function something from outside, sort of like... select x.col_a, y.col1 from mytable x, my_plpgsql_fn(a.val) y where x.col_b = 'foo'; When I try this one, I get a "ERROR Function expression in FROM cannot refer to other relations of same query level" I could accomplish what I need to because Im actually doing all this in the Perl/DBI. And in Perl, I can just run the "x" query in a loop, then the "my_plpgsql_fn" call inside the loop. This will accomplish the same thing, but it'll be 2 queries instead of one (and it'll be far from elegant!) -dave -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Gauthier, Dave Sent: Tuesday, December 16, 2008 3:03 PM To: Andreas Kretschmer; pgsql-general@postgresql.org Subject: Re: [GENERAL] Isolating a record column from a PL-Pgsql function call ? The example I gave was overly simplistic. I actually want to look at more than one of the columns in the returned record, so rewritting it to return an int won't address the need for the other columns. And no, it does not return a set/array of records. It returns just one record. If I run the function outside of a query, it returns something like... "(myvcharval1,myvcharval2,myintval)" Notice the parens, the commas and the lack of single quotes around the "myvcharval(x)" values. I suppose I could do some funky string dissection with this, but I was wondering if there was something more straignt-forward. -dave -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andreas Kretschmer Sent: Tuesday, December 16, 2008 2:51 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Isolating a record column from a PL-Pgsql function call ? Gauthier, Dave schrieb: > Hi: > > > > I have a PL-Pgsql function that returns a record of a type that have 4 > elements > (columns). Let's say the first element/column is called "id_num". Is there a > way I can specify the id_num element of the record returned from iside a > query? > > > > For example, select col1 from thetable where my_plpgsql_fn(col2).id_num = 123; I think, you should rewrite your function to hand over the id_num as additional parameter. But i'm not sure if i understand you corrently. Is this a setof-returning function? Please, show us your real function, maybe someone can help you more. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Isolating a record column from a PL-Pgsql function call ?
"Gauthier, Dave" writes: > I have a PL-Pgsql function that returns a record of a type that have 4 > elements (columns). Let's say the first element/column is called "id_num". > Is there a way I can specify the id_num element of the record returned from > iside a query? > For example, select col1 from thetable where my_plpgsql_fn(col2).id_num = 123; You're just missing some parentheses: select col1 from thetable where (my_plpgsql_fn(col2)).id_num = 123; as indeed is documented here: http://www.postgresql.org/docs/8.3/static/sql-expressions.html#AEN1679 There are messy syntactic reasons for requiring these parens, which I don't recall the details of at the moment ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Isolating a record column from a PL-Pgsql function call ?
Wheeew! OK, that does work. This knowlege creates new options for doing other stuff on my plate. Thanks to all who responded ! -dave -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Sam Mason Sent: Wednesday, December 17, 2008 6:45 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Isolating a record column from a PL-Pgsql function call ? On Tue, Dec 16, 2008 at 01:03:05PM -0700, Gauthier, Dave wrote: > The example I gave was overly simplistic. I actually want to look at > more than one of the columns in the returned record, so rewritting it > to return an int won't address the need for the other columns. And > no, it does not return a set/array of records. It returns just one > record. > > If I run the function outside of a query, it returns something like... > > "(myvcharval1,myvcharval2,myintval)" > > Notice the parens, the commas and the lack of single quotes around the > "myvcharval(x)" values. This is how a value of type record is serialized. When you're inside PG it knows about the structure of the value and gives nice syntax to pull the thing apart in (reasonably) nice ways. To answer your question, if you have a function like: CREATE FUNCTION foo(p INT, OUT x INT, OUT y INT) RETURNS RECORD ... Then I think you want to do something like: SELECT a, x, y FROM ( SELECT t.a, (foo(t.b)).* FROM tbl t WHERE t.c = 10) z; The reason you have to do this, and not have the function in the FROM clause, is because each item in the FROM clause is independent. There's some standard syntax to say when this isn't true, but PG doesn't know about it yet. One other, recently reported[1], caveat is that PG currently evaluates the function for each parameter returned from the SELECT statement it appears in; so in the above example it'll get called twice. It seems possible to work around this by doing the following: SELECT a, (foo).x, (foo).y FROM ( SELECT t.a, foo(t.b) FROM tbl t WHERE t.c = 10) z; As Tom said in another response, the parens are a bit annoying but needed to keep things un-ambiguous. I think it's something to do with schemas; for example the "(foo).x" above is really short for "z.foo.y". This is then ambiguous whether you're referring to what's written above, or referring to table "foo" in schema "z". PG's fix for this was to introduce the brackets and this, in combination with allowing non-ambiguous column names to be referenced without a table name, means we end up with the strange "(foo)" syntax. At least that's my understanding. Sam [1] http://archives.postgresql.org/pgsql-hackers/2008-12/msg00483.php -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Isolating a record column from a PL-Pgsql function call ?
On Tue, Dec 16, 2008 at 01:03:05PM -0700, Gauthier, Dave wrote: > The example I gave was overly simplistic. I actually want to look at > more than one of the columns in the returned record, so rewritting it > to return an int won't address the need for the other columns. And > no, it does not return a set/array of records. It returns just one > record. > > If I run the function outside of a query, it returns something like... > > "(myvcharval1,myvcharval2,myintval)" > > Notice the parens, the commas and the lack of single quotes around the > "myvcharval(x)" values. This is how a value of type record is serialized. When you're inside PG it knows about the structure of the value and gives nice syntax to pull the thing apart in (reasonably) nice ways. To answer your question, if you have a function like: CREATE FUNCTION foo(p INT, OUT x INT, OUT y INT) RETURNS RECORD ... Then I think you want to do something like: SELECT a, x, y FROM ( SELECT t.a, (foo(t.b)).* FROM tbl t WHERE t.c = 10) z; The reason you have to do this, and not have the function in the FROM clause, is because each item in the FROM clause is independent. There's some standard syntax to say when this isn't true, but PG doesn't know about it yet. One other, recently reported[1], caveat is that PG currently evaluates the function for each parameter returned from the SELECT statement it appears in; so in the above example it'll get called twice. It seems possible to work around this by doing the following: SELECT a, (foo).x, (foo).y FROM ( SELECT t.a, foo(t.b) FROM tbl t WHERE t.c = 10) z; As Tom said in another response, the parens are a bit annoying but needed to keep things un-ambiguous. I think it's something to do with schemas; for example the "(foo).x" above is really short for "z.foo.y". This is then ambiguous whether you're referring to what's written above, or referring to table "foo" in schema "z". PG's fix for this was to introduce the brackets and this, in combination with allowing non-ambiguous column names to be referenced without a table name, means we end up with the strange "(foo)" syntax. At least that's my understanding. Sam [1] http://archives.postgresql.org/pgsql-hackers/2008-12/msg00483.php -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general