agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedNULLs and composite types
5+ messages / 3 participants
[nested] [flat]
* NULLs and composite types
@ 2013-12-12 00:19 Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2013-12-12 19:25 ` Re: NULLs and composite types David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Dean Gibson (DB Administrator) @ 2013-12-12 00:19 UTC (permalink / raw)
To: pgsql-sql
PostgreSQL 9.0.2 (CentOS 4.4):
I think the crux of my problem is:
SELECT ROW( NULL, NULL) IS NULL; -- returns TRUE
SELECT COALESCE( ROW( NULL, NULL), ROW( 1,2 )); -- returns "(,)"
Manifestation:
I have a composite type:
CREATE TYPE "BaseTypes"."GeoPosition" AS(
latitude FLOAT,
longitude FLOAT
);
For the problem at hand, I have two tables (say named A and B) which
each declare a field thusly:
...
location" "BaseTypes"."GeoPosition",
...
I also have a PL/pqSQL TRIGGER (BEFORE INSERT) that intercepts INSERTs
to table A generated elsewhere, and depending on a bunch of stuff, may:
1. Change values in NEW fields.
2. Using a CURSOR, go find a related record in table A and update that
instead (and RETURN NULL from the TRIGGER procedure), or just RETURN
NEW.
3. Before RETURNing, the TRIGGER procedure may also INSERT or UPDATE a
related record in table B.
This has all worked beautifully for three years, until I added the above
"location" variable to tables A and B. At the beginning of the TRIGGER
procedure, I have:
IF (NEW.location).latitude IS NULL OR
(NEW.location).longitude IS NULL THEN
NEW.location := NULL;
END IF;
My intent is to make sure that "location" never has the value "ROW(
NULL, NULL)", mainly for subsequent rendering in a web page.
This works in making sure that table A never has the above value.
However, when I INSERT or UPDATE the related record in table B, somehow
the fully "NULL" value for "location" gets "corrupted" into "ROW( NULL,
NULL)". I've spent the better part of a day trying to figure this out,
with statements like ("record_row" comes from a row captured in a CURSOR
SELECT statement from table A):
IF (record_row.location).latitude IS NULL OR
(record_row.location).longitude IS NULL THEN
record_row.location := NULL;
END IF;
IF record_row.location = ROW( NULL, NULL
)::"GeoPosition" THEN
RAISE LOG 'Debug 1';
END IF;
These are six successive lines, and yet the RAISE statement is
frequently executed.
Right now I get rid of the problem by manually (and frequently)
executing the following statement:
UPDATE "A" SET location = NULL WHERE location = ROW( NULL, NULL
)::"GeoPosition";
The above changes the "corrupted" lines correctly, and DOESN'T change
lines where "location" is already fully NULL.
What's going on? I can provide more detail if requested. Of course, an
obvious workaround is to use in a VIEW:
... NULLIF( location, ROW( NULL, NULL )::"GeoPosition" ) ...
but I'd like to know the cause.
ps: I know the word "location" is "non-reserved", and if that's the
problem, I can change it; it just means changing a bunch of other
stuff, which I'd rather not do unless necessary.
-- Dean
--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: NULLs and composite types
2013-12-12 00:19 NULLs and composite types Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
@ 2013-12-12 19:25 ` David Johnston <polobo@yahoo.com>
2013-12-12 21:57 ` Re: NULLs and composite types Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
0 siblings, 1 reply; 5+ messages in thread
From: David Johnston @ 2013-12-12 19:25 UTC (permalink / raw)
To: pgsql-sql
Dean Gibson (DB Administrator)-2 wrote
> What's going on? I can provide more detail if requested. Of course, an
> obvious workaround is to use in a VIEW:
>
> ... NULLIF( location, ROW( NULL, NULL )::"GeoPosition" ) ...
>
> but I'd like to know the cause.
Cannot test right now but the core issue is that IS NULL on a record type
evaluates both the scalar whole and the sub-components. Try using IS [NOT]
DISTINCT FROM with various target expressions and see if you can get
something more sane.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/NULLs-and-composite-types-tp5783027p5783187.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: NULLs and composite types
2013-12-12 00:19 NULLs and composite types Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2013-12-12 19:25 ` Re: NULLs and composite types David Johnston <polobo@yahoo.com>
@ 2013-12-12 21:57 ` Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2013-12-12 23:57 ` Re: NULLs and composite types Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Dean Gibson (DB Administrator) @ 2013-12-12 21:57 UTC (permalink / raw)
To: pgsql-sql
On 2013-12-12 11:25, David Johnston wrote:
> Dean Gibson (DB Administrator)-2 wrote
>> What's going on? I can provide more detail if requested. Of course, an
>> obvious workaround is to use in a VIEW:
>>
>> ... NULLIF( location, ROW( NULL, NULL )::"GeoPosition" ) ...
>>
>> but I'd like to know the cause.
> Cannot test right now but the core issue is that IS NULL on a record type
> evaluates both the scalar whole and the sub-components. Try using IS [NOT]
> DISTINCT FROM with various target expressions and see if you can get
> something more sane.
>
> David J.
Yes, "SELECT ROW( NULL, NULL ) IS NULL;" produces TRUE, and "SELECT ROW(
NULL, NULL ) IS NOT DISTINCT FROM NULL;" produces FALSE.
However, my problem is not that the comparison tests produce different
results; that's just a symptom. My problem is that PostgreSQL is
*changing* a NULL record value, to a record with NULLs for the component
values, when I attempt to INSERT or UPDATE it into a different field.
That means in php (for example), that retrieving what started out as a
NULL record (and in php retrieves an empty string), becomes a record
with NULL values (and in php retrieves a "(,)" string). Yes, I can test
for that in php, but problems/work-arounds need to be solved in the
component that causes them.
However, I have found a satisfactory work-around in the TRIGGER function
to the problem: In my INSERT and UPDATE statements, I use:
... NULLIF( record_row.location, ROW( NULL, NULL )::"GeoPosition" ) ...
when adding or changing a value.
Note that setting "record_row.location" to NULL in PL/pgSQL just before
the INSERT or UPDATE *does not solve the problem*, and tests of the
value before and after setting the value in a record field (retrieved
via a CURSOR FOR SELECT ...) shows that the value does not change to
fully NULL.
--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: NULLs and composite types
2013-12-12 00:19 NULLs and composite types Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2013-12-12 19:25 ` Re: NULLs and composite types David Johnston <polobo@yahoo.com>
2013-12-12 21:57 ` Re: NULLs and composite types Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
@ 2013-12-12 23:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-12-13 04:31 ` Re: NULLs and composite types Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2013-12-12 23:57 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@ultimeth.com>; +Cc: pgsql-sql
"Dean Gibson (DB Administrator)" <postgresql@ultimeth.com> writes:
> However, my problem is not that the comparison tests produce different
> results; that's just a symptom. My problem is that PostgreSQL is
> *changing* a NULL record value, to a record with NULLs for the component
> values, when I attempt to INSERT or UPDATE it into a different field.
I don't think there is any mechanism in core Postgres that would do that.
plpgsql, however, is a different story. It has two different methods for
representing composite-type variables, and only one of those is capable of
representing a a "simple NULL" record value. So I suspect what is
happening is that one of your plpgsql trigger functions is doing something
with the location field that causes it to become a row-of-nulls. You've
not shown us enough detail to pinpoint the problem though.
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: NULLs and composite types
2013-12-12 00:19 NULLs and composite types Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2013-12-12 19:25 ` Re: NULLs and composite types David Johnston <polobo@yahoo.com>
2013-12-12 21:57 ` Re: NULLs and composite types Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2013-12-12 23:57 ` Re: NULLs and composite types Tom Lane <tgl@sss.pgh.pa.us>
@ 2013-12-13 04:31 ` Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
0 siblings, 0 replies; 5+ messages in thread
From: Dean Gibson (DB Administrator) @ 2013-12-13 04:31 UTC (permalink / raw)
To: pgsql-sql
On 2013-12-12 15:57, Tom Lane wrote:
> "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com> writes:
>> However, my problem is not that the comparison tests produce different
>> results; that's just a symptom. My problem is that PostgreSQL is
>> *changing* a NULL record value, to a record with NULLs for the component
>> values, when I attempt to INSERT or UPDATE it into a different field.
> I don't think there is any mechanism in core Postgres that would do that.
>
> plpgsql, however, is a different story. It has two different methods for
> representing composite-type variables, and only one of those is capable of
> representing a a "simple NULL" record value. So I suspect what is
> happening is that one of your plpgsql trigger functions is doing something
> with the location field that causes it to become a row-of-nulls. You've
> not shown us enough detail to pinpoint the problem though.
>
> regards, tom lane
Yeah, I suspected that it was a peculiarity of PL/pgSQL.
If I remove the "NULLIF" from the INSERT and UPDATE on
"D-Star"."_CallSession" statements (below), "incorrect" (non-fully-NULL)
values are inserted. Strangely, this problem does not occur on the
UPDATE or (implied) INSERT on "D-Star"."_CallRecord". There are TABLE
INDEXes, but none of them involve the "location" field.
Here's the component and table definition:
CREATE TYPE "BaseTypes"."GeoPosition" AS(
latitude FLOAT,
longitude FLOAT
);
CREATE TABLE "_CallRecord"(
my_call CHAR( 7 ) NOT NULL,
my_ssid CHAR( 2 ) NOT NULL,
session_begin TIMESTAMP WITH TIME ZONE NOT NULL,
first_tx TIMESTAMP WITH TIME ZONE NOT NULL,
last_tx TIMESTAMP WITH TIME ZONE,
ircddb_tx TIMESTAMP WITH TIME ZONE,
rpt1_call CHAR( 7 ),
rpt1_term CHAR( 1 ),
rpt2_call CHAR( 7 ),
rpt2_term CHAR( 1 ),
your_call CHAR( 7 ),
your_term CHAR( 1 ),
ur_call CHAR( 7 ),
ur_term CHAR( 1 ),
dest_call CHAR( 7 ),
dest_term CHAR( 1 ),
tag CHAR( 4 ),
dstar_msg CHAR( 20 ),
flag0 BIT( 4 ),
flag1 BIT( 8 ),
flag2 BIT( 8 ),
flag3 BIT( 8 ),
flag4 BIT( 8 ),
frames INTEGER,
silent_pct SMALLINT,
bit_err_pct_x10 SMALLINT,
unproto CHAR( 6 ),
location "BaseTypes"."GeoPosition",
altitude_ft SMALLINT,
course SMALLINT,
knots SMALLINT,
symbol CHAR( 2 ),
aprs_dhms CHAR( 7 ),
aprs_msg VARCHAR
);
CREATE TABLE "_CallSession"(
my_call CHAR( 7 ) NOT NULL,
my_ssid CHAR( 2 ) NOT NULL,
session_begin TIMESTAMP WITH TIME ZONE NOT NULL,
session_end TIMESTAMP WITH TIME ZONE NOT NULL,
rpt1_call CHAR( 7 ),
rpt1_term CHAR( 1 ),
rpt2_call CHAR( 7 ),
rpt2_term CHAR( 1 ),
your_call CHAR( 7 ),
your_term CHAR( 1 ),
dest_call CHAR( 7 ),
dest_term CHAR( 1 ),
tag CHAR( 4 ),
dstar_msg CHAR( 20 ),
bit_err_pct_x10 SMALLINT,
unproto CHAR( 6 ),
location "BaseTypes"."GeoPosition",
altitude_ft SMALLINT,
course SMALLINT,
knots SMALLINT,
symbol CHAR( 2 ),
aprs_msg VARCHAR,
cnt INTEGER DEFAULT 1 NOT NULL,
active BOOLEAN DEFAULT TRUE NOT NULL
);
Here's the trigger function ("BEFORE INSERT"):
CREATE OR REPLACE FUNCTION "Callsign"."D-StarInsert"()
RETURNS TRIGGER
CALLED ON NULL INPUT
VOLATILE
LANGUAGE PLpgSQL AS $PLpgSQL$
DECLARE
tx_delay INTERVAL := NEW.first_tx - NEW.ircddb_tx;
record_found BOOLEAN;
record_row "D-Star"."_CallRecord"%ROWTYPE;
record_cursor CURSOR( delay FLOAT )
FOR SELECT *
FROM "D-Star"."_CallRecord"
WHERE my_call = NEW.my_call
AND my_ssid = NEW.my_ssid
AND last_tx + INTERVAL '1 second' *
GREATEST( frames * 0.02 + 2.0, delay )
> NEW.first_tx
-- Estimate duration from frame count
ORDER BY last_tx DESC
LIMIT 1;
session_found BOOLEAN;
session_row "D-Star"."_CallSession"%ROWTYPE;
session_cursor CURSOR
FOR SELECT *
FROM "D-Star"."_CallSession"
WHERE my_call = NEW.my_call
AND my_ssid = NEW.my_ssid
AND active;
session_match BOOLEAN;
BEGIN
IF NEW.my_call = '*******' THEN
RAISE NOTICE 'Hidden callsign TX on
RPT1=''%'' discarded', NEW.rpt1_call || NEW.rpt1_term;
RETURN NULL;
END IF;
IF tx_delay NOT BETWEEN INTERVAL '-0.5 second' AND
INTERVAL '+10 seconds' THEN
RAISE NOTICE 'RPT=''%'' {%} clock (%) %',
COALESCE( NEW.rpt2_call,
NEW.rpt1_call ),
NEW.ircddb_tx,
CASE
WHEN tx_delay < INTERVAL '0'
THEN 'unsynchronized'
ELSE 'excessive delay'
END,
tx_delay;
RETURN NULL;
END IF;
IF NEW.location = ROW( NULL, NULL )::"GeoPosition"
THEN
RAISE NOTICE 'New record: NULL location
components = %',
NEW.location; -- Can this happen?
END IF;
IF (NEW.location).latitude IS NULL OR
(NEW.location).longitude IS NULL THEN
NEW.location := NULL; -- This does
work !!!
END IF;
IF NEW.unproto IS NOT NULL THEN
NEW.my_ssid := COALESCE( NEW.my_ssid, '' );
NEW.rpt1_term := COALESCE( NEW.rpt1_term, '' );
NEW.aprs_msg := COALESCE( NEW.aprs_msg, '' );
OPEN record_cursor( 10.0 );
ELSE
NEW.rpt2_call := COALESCE( NEW.rpt2_call, ''
); -- Implied by missing gateway
OPEN record_cursor( 5.0 );
END IF;
FETCH record_cursor INTO record_row;
record_found := FOUND;
OPEN session_cursor;
FETCH session_cursor INTO session_row;
session_found := FOUND;
NEW.last_tx := NEW.first_tx;
NEW.session_begin := NEW.first_tx;
NEW.rpt2_term := CASE
WHEN NEW.rpt2_call IS NULL
THEN NULL
ELSE COALESCE(
NEW.rpt2_term, '' )
END;
NEW.your_call := CASE RTRIM( NEW.ur_call ||
NEW.ur_term )
WHEN '' THEN 'CQCQCQ'
WHEN '/' THEN '/CQCQCQ'
ELSE NULLIF( NEW.ur_call,
'*******' )
END;
NEW.your_term := CASE
WHEN NEW.your_call IS NULL
THEN NULL
ELSE COALESCE( NEW.ur_term, '' )
END;
NEW.dest_term := CASE
WHEN NEW.dest_call IS NULL
THEN NULL
ELSE COALESCE(
NEW.dest_term, '' )
END;
session_match := session_row.rpt1_call =
NEW.rpt1_call AND
session_row.rpt1_term =
NEW.rpt1_term AND
(session_row.your_call =
NEW.your_call) IS NOT FALSE AND
(session_row.your_term =
NEW.your_term) IS NOT FALSE AND
(session_row.tag =
NEW.tag) IS NOT FALSE AND
(session_row.unproto =
NEW.unproto) IS NOT FALSE;
IF session_found AND
session_match THEN
NEW.session_begin := session_row.session_begin;
END IF;
IF record_found THEN
-- IF record_row.location = ROW( NULL, NULL
)::"GeoPosition" THEN
-- RAISE LOG 'Matched record: NULL
location components = %',
-- record_row.location; -- Diagnostic only
-- END IF;
-- Since the dstar_msg and bit_err_pct come in
separate records,
-- this is the best we can do without saving
two dstar_msg values.
UPDATE "D-Star"."_CallRecord"
SET your_call = COALESCE(
NEW.your_call, your_call ),
your_term = COALESCE(
NEW.your_term, your_term ),
ur_call = COALESCE(
NEW.ur_call, ur_call ),
ur_term = COALESCE(
NEW.ur_term, ur_term ),
rpt1_call = COALESCE(
NEW.rpt1_call, rpt1_call ),
rpt1_term = COALESCE(
NEW.rpt1_term, rpt1_term ),
rpt2_call = COALESCE(
NEW.rpt2_call, rpt2_call ),
rpt2_term = COALESCE(
NEW.rpt2_term, rpt2_term ),
dest_call = COALESCE(
NEW.dest_call, dest_call ),
dest_term = COALESCE(
NEW.dest_term, dest_term ),
tag = COALESCE(
NEW.tag, tag ),
dstar_msg = COALESCE(
NEW.dstar_msg, dstar_msg ),
bit_err_pct_x10 = COALESCE(
NEW.bit_err_pct_x10, bit_err_pct_x10 ),
silent_pct = GREATEST(
NEW.silent_pct, silent_pct ),
last_tx = GREATEST(
NEW.last_tx, last_tx ),
flag0 = COALESCE(
NEW.flag0, flag0 ),
flag1 = COALESCE(
NEW.flag1, flag1 ),
flag2 = COALESCE(
NEW.flag2, flag2 ),
flag3 = COALESCE(
NEW.flag3, flag3 ),
flag4 = COALESCE(
NEW.flag4, flag4 ),
ircddb_tx = LEAST(
NEW.ircddb_tx, ircddb_tx ),
frames = COALESCE(
NEW.frames, 1 )
+ COALESCE(
frames, 0 ),
unproto = COALESCE(
NEW.unproto, unproto ),
location = COALESCE(
NEW.location, location ),
altitude_ft = COALESCE(
NEW.altitude_ft, altitude_ft ),
course = COALESCE(
NEW.course, course ),
knots = COALESCE(
NEW.knots, knots ),
symbol = COALESCE(
NEW.symbol, symbol ),
aprs_dhms = COALESCE(
NEW.aprs_dhms, aprs_dhms ),
aprs_msg = COALESCE(
NEW.aprs_msg, aprs_msg )
WHERE CURRENT OF record_cursor
RETURNING * INTO record_row; --
RETURNING clause required for subsequent data
-- IF record_row.location = ROW( NULL, NULL
)::"GeoPosition" THEN
-- RAISE LOG 'Updated record: NULL
location components = %',
-- record_row.location; -- Diagnostic only
-- END IF;
NEW := NULL;
ELSE
IF NEW.unproto IS NOT NULL THEN
RAISE LOG '%|%-%/%|%|%|%',
DATE_TRUNC( 'Second',
NEW.first_tx ) AT TIME ZONE 'UTC',
RPAD( NEW.my_call, 7 ), RPAD( NEW.my_ssid, 2),
RPAD( COALESCE(
NEW.unproto, NEW.tag, '' ), 6 ),
RPAD( NEW.rpt1_call, 7 ) || RPAD( NEW.rpt1_term, 1 ),
COALESCE( RPAD(
NEW.rpt2_call, 7 ) || RPAD( NEW.rpt2_term, 1 ),
' (APRS) ' ),
COALESCE( NULLIF(
NEW.aprs_msg, '' ), NEW.dstar_msg, '' );
ELSE
RAISE LOG '%|%-%/%|%|%|%@%|%',
DATE_TRUNC( 'Second',
NEW.first_tx ) AT TIME ZONE 'UTC',
RPAD( NEW.my_call, 7 ), RPAD( NEW.my_ssid, 2),
RPAD( COALESCE(
NEW.unproto, NEW.tag, '' ), 6 ),
RPAD( NEW.rpt1_call, 7 ) || RPAD( NEW.rpt1_term, 1 ),
RPAD( NEW.rpt2_call, 7 ) || RPAD( NEW.rpt2_term, 1 ),
RPAD( COALESCE( RPAD(
NEW.ur_call, 7 ) || NEW.ur_term, '' ), 8 ),
RPAD( COALESCE( RPAD(
NEW.dest_call, 7 ) || NEW.dest_term, '' ), 8 ),
COALESCE( NULLIF(
NEW.aprs_msg, '' ), NEW.dstar_msg, '' );
END IF;
record_row := NEW;
-- IF record_row.location = ROW( NULL, NULL
)::"GeoPosition" THEN
-- RAISE LOG 'Replaced record: NULL
location components = %',
-- record_row.location; -- Diagnostic only
-- END IF;
END IF;
CLOSE record_cursor;
IF session_found THEN
IF session_match OR
session_row.session_begin =
record_row.session_begin THEN
-- Since the bit_err_pct update comes
after the dstar_msg update, this works fine.
UPDATE "D-Star"."_CallSession"
SET your_call =
record_row.your_call,
your_term =
record_row.your_term,
rpt1_call =
record_row.rpt1_call,
rpt1_term =
record_row.rpt1_term,
rpt2_call = COALESCE(
record_row.rpt2_call, rpt2_call ),
rpt2_term = COALESCE(
record_row.rpt2_term, rpt2_term ),
dest_call = COALESCE(
record_row.dest_call, dest_call ),
dest_term = COALESCE(
record_row.dest_term, dest_term ),
tag = COALESCE(
record_row.tag, tag ),
session_end = GREATEST(
record_row.last_tx, session_end ),
bit_err_pct_x10 = LEAST(
record_row.bit_err_pct_x10, bit_err_pct_x10 ),
dstar_msg = CASE LEAST(
record_row.bit_err_pct_x10, bit_err_pct_x10 )
WHEN record_row.bit_err_pct_x10
THEN COALESCE( record_row.dstar_msg, dstar_msg )
ELSE COALESCE( dstar_msg, record_row.dstar_msg )
END,
unproto = COALESCE(
record_row.unproto, unproto ),
location = NULLIF(
COALESCE( record_row.location, location ),
ROW( NULL, NULL )::"GeoPosition" ),
altitude_ft = COALESCE(
record_row.altitude_ft, altitude_ft ),
course = COALESCE(
record_row.course, course ),
knots = COALESCE(
record_row.knots, knots ),
symbol = COALESCE(
record_row.symbol, symbol ),
aprs_msg = COALESCE(
record_row.aprs_msg, aprs_msg ),
cnt = cnt + CAST(
NOT record_found AS INTEGER)
WHERE CURRENT OF session_cursor
RETURNING * INTO session_row;
-- RETURNING clause only needed for diagnostics
-- IF session_row.location = ROW( NULL,
NULL )::"GeoPosition" THEN
-- RAISE LOG 'Updated session:
NULL location components = %',
-- session_row.location; -- Diagnostic only
-- END IF;
CLOSE session_cursor;
RETURN NEW;
ELSIF NOT record_found THEN
UPDATE "D-Star"."_CallSession"
SET active = FALSE
WHERE CURRENT OF session_cursor;
END IF;
END IF;
CLOSE session_cursor;
IF NOT record_found THEN
INSERT INTO "D-Star"."_CallSession"(
my_call,
my_ssid,
session_begin,
session_end,
rpt1_call,
rpt1_term,
rpt2_call,
rpt2_term,
your_call,
your_term,
dest_call,
dest_term,
tag,
dstar_msg,
bit_err_pct_x10,
unproto,
location,
altitude_ft,
course,
knots,
symbol,
aprs_msg )
VALUES( record_row.my_call,
record_row.my_ssid,
record_row.session_begin,
record_row.session_begin, -- Initial session_end value
record_row.rpt1_call,
record_row.rpt1_term,
record_row.rpt2_call,
record_row.rpt2_term,
record_row.your_call,
record_row.your_term,
record_row.dest_call,
record_row.dest_term,
record_row.tag,
record_row.dstar_msg,
record_row.bit_err_pct_x10,
record_row.unproto,
NULLIF( record_row.location,
ROW( NULL, NULL )::"GeoPosition" ),
record_row.altitude_ft,
record_row.course,
record_row.knots,
record_row.symbol,
record_row.aprs_msg )
RETURNING * INTO session_row;
-- RETURNING clause only needed for diagnostics
-- IF session_row.location = ROW( NULL, NULL
)::"GeoPosition" THEN
-- RAISE LOG 'Inserted session: NULL
location components = %',
-- session_row.location; -- Diagnostic only
-- END IF;
ELSE
RAISE WARNING '[ircsql/%-%] Duplicate INSERT
not in current myCall session',
record_row.my_call,
record_row.my_ssid;
END IF;
RETURN NEW;
END;
$PLpgSQL$
--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2013-12-13 04:31 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-12-12 00:19 NULLs and composite types Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2013-12-12 19:25 ` David Johnston <polobo@yahoo.com>
2013-12-12 21:57 ` Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2013-12-12 23:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-12-13 04:31 ` Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox