Re: [SQL] Add: Special sort querstion
I think it can not be done with default PostgreSQL functions and operators, because you must compare two different columns of two rows depending which row is on either side of the comparision. But I think you can do this with the following steps: 1) create a new type as a 4-tupel of start_lat, end_lat, start_lng, end_lng. 2) write a comparison function for this type 3) write a SQL-Function to convert 4 values into your new type (for example: ToMyType(start_lat, end_lat, start_lnd, end_lng) returns MyType...) 4) use ToMyType in the order clause of your select If this would work, I'm interested in a working example code :-) -- --- Dirk Jagdmann http://cubic.org/~doj - http://llg.cubic.org -- 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] Add: Special sort querstion
I think it can not be done with default PostgreSQL functions and operators, because you must compare two different columns of two rows depending which row is on either side of the comparision. But I think you can do this with the following steps: 1) create a new type as a 4-tupel of start_lat, end_lat, start_lng, end_lng. 2) write a comparison function for this type 3) write a SQL-Function to convert 4 values into your new type (for example: ToMyType(start_lat, end_lat, start_lnd, end_lng) returns MyType...) 4) use ToMyType in the order clause of your select If this would work, I'm interested in a working example code :-) -- --- Dirk Jagdmann http://cubic.org/~doj - http://llg.cubic.org -- 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] Add: Special sort querstion
On Fri, Mar 27, 2009 at 6:10 AM, Dominik Piekarski d.piekar...@vivawasser.de wrote: Oh, actually every row of the same id-range has the same start_lat/start_lng coordinates as the predecessors end_lat/end_lng coordinates. But the question remains the same. Is there a way to do something like ORDER BY (start_lat = end_lat AND start_lng = end_lng) ? Or maybe another way to achieve the same result? Would something like order by start_lat-endlat, start_lng-end_lng OR case when start_lat=end_lat AND start_lng=end_lng then 0 else 1 end ??? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Stored function not accepting null value?
I am using a stored function to determine whether a table needs an update or insert, based on the merge_db function in the postgres documentation. (Not currently including it below because it's rather long, but other than the fact that it contains 27 values, it's pretty much the same as that example.) It works fine as long as all the values are defined: select event_archive (7, 2, 2, 2, 3, text 'TIMS', 3, text 'I-90', 2, 3, 1.2, 1.3, 3, 1, 2, text 'important', timestamp '2008-01-01', timestamp '2008-01-01', timestamp '2008-01-01', timestamp '2008-01-01', timestamp '2008-01-01', text '2 hours', timestamp '2008-01-01', timestamp '2008-01-01', text 'oak', text 'main', 2); but if I change any of the above values to null, the function simply doesn't operate. select event_archive (7, 2, 2, 2, 3, text 'TIMS', 3, text 'I-90', 2, 3, 1.2, 1.3, 3, 1, 2, text 'important', timestamp '2008-01-01', timestamp '2008-01-01', timestamp '2008-01-01', timestamp '2008-01-01', timestamp '2008-01-01', text '2 hours', timestamp '2008-01-01', timestamp '2008-01-01', text 'oak', text 'main', null); It returns: Total query runtime: 0 ms. 1 rows retrieved. versus a runtime of about 13ms when it successfully updates or inserts. I put raise notices into the function to try to debug, and it never even hits the first one in the first LOOP when there is a null value. The only column in the table that is declared not null would be the 7 in the above statement (id), all other fields should be able to accept a null value. Is this a known limitation with functions, or am I doing something incorrectly? Thanks, DHall This communication may contain information that is confidential, privileged or subject to copyright. If you are not the intended recipient, please advise by return e-mail and delete the message and any attachments immediately without reading, copying or forwarding to others.
Re: [SQL] Stored function not accepting null value?
Deirdre Hall d.h...@delcan.com writes: I am using a stored function to determine whether a table needs an update or insert, based on the merge_db function in the postgres documentation. (Not currently including it below because it's rather long, but other than the fact that it contains 27 values, it's pretty much the same as that example.) Well, not including it is a good way to ensure that no one will really know what the problem is ... but I wonder whether you declared the function STRICT. That'd prevent it from being called with a NULL argument, which seems to fit the reported behavior. regards, tom lane -- 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] Stored function not accepting null value?
And that would be where I went wrong. Thanks. DHall -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Wednesday, April 01, 2009 2:01 PM To: Deirdre Hall Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Stored function not accepting null value? Deirdre Hall d.h...@delcan.com writes: I am using a stored function to determine whether a table needs an update or insert, based on the merge_db function in the postgres documentation. (Not currently including it below because it's rather long, but other than the fact that it contains 27 values, it's pretty much the same as that example.) Well, not including it is a good way to ensure that no one will really know what the problem is ... but I wonder whether you declared the function STRICT. That'd prevent it from being called with a NULL argument, which seems to fit the reported behavior. regards, tom lane This communication may contain information that is confidential, privileged or subject to copyright. If you are not the intended recipient, please advise by return e-mail and delete the message and any attachments immediately without reading, copying or forwarding to others. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] FUNCTION problem
Hello, I am having a problem with a FUNCTION. The function creates just fine with no errors. However, when I call the function postgres produces an error. Perhaps someone can enlighten me. --I can reproduce the error by making a test function --that is much easier to follow that the original: CREATE OR REPLACE FUNCTION test_function(integer) RETURNS SETOF RECORD AS $BODY$ DECLARE croid integer; BEGIN --PERFORM A SMALL CALCULATION --DOESNT SEEM TO MATTER WHAT IT IS SELECT INTO croid 2; --A SELECT STATEMENT OUTPUTS RECORDS (one in this case) SELECT croid,$1; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE --The call looks like the following: SELECT test_function(1); --The resulting error reads as follows: ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function test_function line 5 at SQL statement ** Error ** ERROR: query has no destination for result data SQL state: 42601 Hint: If you want to discard the results of a SELECT, use PERFORM instead. Context: PL/pgSQL function test_function line 5 at SQL statement -- 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 problem
On Wednesday 01 April 2009 4:31:20 pm Peter Willis wrote: Hello, I am having a problem with a FUNCTION. The function creates just fine with no errors. However, when I call the function postgres produces an error. Perhaps someone can enlighten me. --I can reproduce the error by making a test function --that is much easier to follow that the original: CREATE OR REPLACE FUNCTION test_function(integer) RETURNS SETOF RECORD AS $BODY$ DECLARE croid integer; BEGIN --PERFORM A SMALL CALCULATION --DOESNT SEEM TO MATTER WHAT IT IS SELECT INTO croid 2; --A SELECT STATEMENT OUTPUTS RECORDS (one in this case) SELECT croid,$1; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE --The call looks like the following: SELECT test_function(1); --The resulting error reads as follows: ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function test_function line 5 at SQL statement ** Error ** ERROR: query has no destination for result data SQL state: 42601 Hint: If you want to discard the results of a SELECT, use PERFORM instead. Context: PL/pgSQL function test_function line 5 at SQL statement You have declared function to RETURN SETOF. In order for that to work you need to do RETURN NEXT. See below for difference between RETURN and RETURN NEXT: http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
