Обсуждение: Greatest/Least functions?
As far as I can tell, Postgres has no equivalent to greatest and least functions in Oracle. Yes, you can do the same thing with a case statement, but at the expense of writing MUCH longer SQL statements. Is this something that is on or can be added to the 'to do' list? I could write a series of user-defined functions to do specific comparisons (such as comparing several dates and returning the greatest one) but there doesn't appear to be a way to write a user function with a variable number of parameters, either, so I guess I'd have to define a series of them with 2,3,4,... parameters. -- Mike Nolan
> As far as I can tell, Postgres has no equivalent to greatest and least > functions in Oracle. Doesn't max/min() do that ? Note that I know nothing about greatest/least in Oracle. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Mike Nolan wrote: > As far as I can tell, Postgres has no equivalent to greatest and least > functions in Oracle. Yes, you can do the same thing with a case statement, > but at the expense of writing MUCH longer SQL statements. > > Is this something that is on or can be added to the 'to do' list? > > I could write a series of user-defined functions to do specific > comparisons (such as comparing several dates and returning the greatest > one) but there doesn't appear to be a way to write a user function with a > variable number of parameters, either, so I guess I'd have to define a > series of them with 2,3,4,... parameters. There was a thread on this last year in July -- see: http://archives.postgresql.org/pgsql-sql/2003-07/msg00001.php It doesn't seem to have made it into the archives, but I posted this solution to the SQL list on 2 July, 2003: ----------------------------- create or replace function make_greatest() returns text as ' declare v_args int := 32; v_first text := ''create or replace function greatest(anyelement, anyelement) returns anyelement as ''''select case when $1 > $2 then $1 else $2 end'''' language ''''sql''''''; v_part1 text := ''create or replace function greatest(anyelement''; v_part2 text := '') returns anyelement as ''''select greatest($1, greatest($2''; v_part3 text := ''))'''' language ''''sql''''''; v_sql text; begin execute v_first; for i in 3 .. v_args loop v_sql := v_part1; for j in 2 .. i loop v_sql := v_sql || '',anyelement''; end loop; v_sql := v_sql || v_part2; for j in 3 .. i loop v_sql := v_sql || '',$'' || j::text; end loop; v_sql := v_sql || v_part3; execute v_sql; end loop; return ''OK''; end; ' language 'plpgsql'; select make_greatest(); Now you should have 31 "greatest" functions, accepting from 2 to 32 arguments. *Not* heavily tested, but seemed to work for me. regression=# select greatest(112,2,3,4,5,6,7,8,9,10,1234,2,3,4,5,66,7,8,9,10,1,27,3,4,5,6,347,8,9,10,1,2); greatest ---------- 1234 (1 row) ----------------------------- Here are more examples: regression=# select greatest('c'::text,'a','Z','%'); greatest ---------- c (1 row) regression=# select greatest(now(),'today','tomorrow'); greatest ------------------------ 2004-08-23 00:00:00-07 (1 row) regression=# explain analyze select greatest(112,2,3,4,5,6,7,8,9,10,1234,2,3,4,5,66,7,8,9,10,1,27,3,4,5,6,347,8,9,10,1,2); QUERY PLAN ------------------------------------------------------------------------------------ Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.007..0.009 rows=1 loops=1) Total runtime: 0.049 ms (2 rows) SQL function inlining in pg >=7.4 rewrites ensures there isn't even function call overhead to be concerned with. HTH, Joe
> > As far as I can tell, Postgres has no equivalent to greatest and least > > functions in Oracle. > Doesn't max/min() do that ? Note that I know nothing about > greatest/least in Oracle. No, max/min are aggregate functions. Greatest allows you to select the largest of a series of terms. Here's a simple example: greatest(1,2,3,4,5,6) would return 6 Here's a bit more useful one: greatest(field1,field2,field3) would return the largest value from the three supplied fields from the current row. Writing a case statement to select the largest from among 3 or more values gets a bit complicated. -- Mike Nolan
On Sun, Aug 22, 2004 at 12:35:20PM -0500, Mike Nolan wrote: > > > As far as I can tell, Postgres has no equivalent to greatest and least > > > functions in Oracle. > > Doesn't max/min() do that ? Note that I know nothing about > > greatest/least in Oracle. > > No, max/min are aggregate functions. Greatest allows you to select > the largest of a series of terms. > > Here's a simple example: > > greatest(1,2,3,4,5,6) would return 6 > > Here's a bit more useful one: > > greatest(field1,field2,field3) would return the largest value from the > three supplied fields from the current row. Postgresql does however have the 2-argument versions: int4larger, int4smaller floatlarger, floatsmaller Not seen them mentioned much, but they're very useful... Of course, after a while even: int4larger( int4larger( field1, field2 ), int4larger( field3, field4 ) ) gets tiring. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Вложения
Why does =ANY() need an extra cast when used on an array returned by a select?
От
Frank van Vugt
Дата:
Hi, The following works : db=# select 1 = ANY ('{1,2,3}'::int[]); ?column? ---------- t (1 row) This doesn't : db=# select 1 = ANY (select '{1,2,3}'::int[]); ERROR: operator does not exist: integer = integer[] HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. Using an extra case, the above can easily be made to work : db=# select 1 = ANY ((select '{1,2,3}'::int[])::int[]); ?column? ---------- t (1 row) I'm just wondering why the array returned by the inner select is not casted by ANY() automagically? db=# select version(); version --------------------------------------------------------------------- PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 (1 row) -- Best, Frank.
On Mon, 23 Aug 2004, Frank van Vugt wrote: > The following works : > > db=# select 1 = ANY ('{1,2,3}'::int[]); > ?column? > ---------- > t > (1 row) > > > This doesn't : > > db=# select 1 = ANY (select '{1,2,3}'::int[]); > ERROR: operator does not exist: integer = integer[] > HINT: No operator matches the given name and argument type(s). You may need > to add explicit type casts. > > Using an extra case, the above can easily be made to work : > > db=# select 1 = ANY ((select '{1,2,3}'::int[])::int[]); > ?column? > ---------- > t > (1 row) > > > I'm just wondering why the array returned by the inner select is not casted by > ANY() automagically? Barring the cast syntax and such, the first and last query would I believe be illegal in SQL92/99, so we defined useful behavior for them for this case. The second query looks to me to be of the form = ANY (table subquery) which already had defined behavior by spec. Changing it to act like the first or last would break that spec behavior.
> > works =# select 1 = ANY ('{1,2,3}'::int[]); > > doesn't work =# select 1 = ANY (select '{1,2,3}'::int[]); > > works =# select 1 = ANY ((select '{1,2,3}'::int[])::int[]); I may be misinterpreting your reply but..... My real-world application has a set-returning PL/pgSQL function for which I created a type, so the function is returning rows of this type. One of the fields in this type is an array of int. > The second query looks to me to be of the form = ANY (table > subquery) which already had defined behavior by spec. Yes, what I want is to be able to do something like: select some_fields from some_table where some_int = ANY( select field_of_type_array_of_int from plpgsql_method_returning_custom_type where we_just_return_a_single_record); But this won't work, so I'm not quite getting what you mean by 'which already had defined behavior by spec' > Changing it to act like the first or last would break that spec behavior. Ok, but I'm mainly looking for the 'proper' way to make this work, not necessarily using a syntax like the first or last example. -- Best, Frank.
On Mon, 23 Aug 2004, Frank van Vugt wrote: > > > works =# select 1 = ANY ('{1,2,3}'::int[]); > > > doesn't work =# select 1 = ANY (select '{1,2,3}'::int[]); > > > works =# select 1 = ANY ((select '{1,2,3}'::int[])::int[]); > > I may be misinterpreting your reply but..... > > My real-world application has a set-returning PL/pgSQL function for which I > created a type, so the function is returning rows of this type. One of the > fields in this type is an array of int. > > > The second query looks to me to be of the form = ANY (table > > subquery) which already had defined behavior by spec. > > Yes, what I want is to be able to do something like: > > select some_fields > from some_table > where some_int = ANY( > select field_of_type_array_of_int > from plpgsql_method_returning_custom_type > where we_just_return_a_single_record); > > But this won't work, so I'm not quite getting what you mean by 'which already > had defined behavior by spec' SQL92/99 basically defines A = ANY (table subquery) to mean For each row returned by the subquery, compare A to the column using the = operator We defined on top of that something like A = ANY (array expression) to mean For each element in the array compare A to the array element using the = operator. If we made, A = ANY (select arraycol ...) to mean the latter, queries that were using it as the former would change meaning from their already defined SQL behavior. Perhaps if you wanted to define it as <non array type> = ANY (select arraycol ...) it might be okay, but right now changing that would mean that you couldn't do select arraycol = ANY(select arraycol from table) I think your third query (with the cast) would be the "correct" way to indicate the intent. That is effectively A = ANY (CAST(scalar subquery AS array type)).