[SQL] A Not Join

[L van der Walt]

I have three table:
Users - Contains username, ID etc...
Permissions - A permission name and ID
Link up table - The user.id and permission.id

If a user.id and a permission.id row exists in the linkuptable the user 
have that permission granted.


With the statement below I can see the permissions a user have.

SELECT users.username, permissions.name
FROM users INNER JOIN linkuptable
 ON (users.id = linkuptable.userid)
INNER JOIN permissions
 ON (permissions.id = linkuptable.permissionid)
WHERE users.username = 'DummyUser'

How do I see the permissions that user DON'T have with a fast SQL statement.

Thus, a NOT the statement for the above SQL statement

Regards

Lani


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: Fwd: Re: [SQL] Referencing

[Daryl Richter]

[EMAIL PROTECTED] wrote:

Quoting Daryl Richter [EMAIL PROTECTED]:


[EMAIL PROTECTED] wrote:
 Quoting Daryl Richter [EMAIL PROTECTED]:
 It's hard to say without knowing more precisely what you are trying to
 model, but I think this push you in the right direction:

 Okay, but references between (output/input) and ACTIVITY tables is 1 
to N.

 OUTPUT/INPUT - 1
 to
 ACTIVITY - N.
 And not N to 1 how the example.
 Then the reference field need to be on ACTIVITY (send/buy) table.


Ahh, ok.  In that case I reverse it like so:

-- This table hold everything in common for inputs/outputs
create table transfer(
idserial   primary key
);



Yes, I think it was what I wanted.
And how I check if a register in Transfer table is only referenciable 
by ONE

table (OR output OR input)?? Would I create a Trigger like:
CREATE or REPLACE FUNCTION TG_output_check() RETURNS TRIGGER AS
$$
 BEGIN
  IF exists (select 1 from input where transfer_id=NEW.transfer_id) THEN
   Raise Exception 'This activity (transfer) is alread setted to INPUT';
  END IF;
  RETURN NEW;
 END;
$$ language 'plpgsql'; CREATE TRIGGER TG_output_check BEFORE INSERT or 
UPDATE

on OUTPUT EXECUTE PROCEDURE TG_output_check();
CREATE or REP...--- and the some function to INPUT ---

Or is there another way to check it?

Thank you again.



Exactly, except for the small change that your trigger declaration needs 
for each row as shown below:


CREATE TRIGGER TG_output_check BEFORE INSERT or UPDATE on output
for each row EXECUTE PROCEDURE TG_output_check();

--
Daryl


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] [GENERAL] A Not Join

[Jim C. Nasby]

On Tue, Nov 01, 2005 at 04:27:01PM +0200, L van der Walt wrote:
 I have three table:
 Users - Contains username, ID etc...
 Permissions - A permission name and ID
 Link up table - The user.id and permission.id
 
 If a user.id and a permission.id row exists in the linkuptable the user 
 have that permission granted.
 
 With the statement below I can see the permissions a user have.
 
 SELECT users.username, permissions.name
 FROM users INNER JOIN linkuptable
  ON (users.id = linkuptable.userid)
 INNER JOIN permissions
  ON (permissions.id = linkuptable.permissionid)
 WHERE users.username = 'DummyUser'
 
 How do I see the permissions that user DON'T have with a fast SQL statement.
 
 Thus, a NOT the statement for the above SQL statement

LEFT JOIN permissions ON (...)
WHERE permissions.id IS NULL

You might have to do the NULL check in a HAVING clause instead... try
it.

BTW, this is probably better asked on pgsql-sql.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[SQL] Index lookup on and criteria

[David Durham]

Apologies if this questions is asked often.  I'm doing some select 
statements based on a timestamp field.  I have an index on the field, 
and when I use the '=' operator the index is used.  However, if I use 
the '' or '' operators, then it does a full table scan.  I've got 
around 6 million rows, so I would think that an index scan would be more 
appropriate.



Here are the statements I'm looking at:

   select * from myTable where myTimeStamp = '10/1/2005';

uses an index.

   select max(myTimeStamp) from myTable;
   select * from myTable where myTimeStamp  '10/2/2005';
   select * from myTable where myTimeStamp  '10/2/2005' and myTimeStamp
   = '10/1/2005';

do not use indexes.  Can anyone point me to some info about what's going 
on?  I've started reading through the manual (chapter 13) which I think 
explains query optimizing, index usage etc.  It seems like this would be 
a common enough problem that it would have a relatively simple solution. 
 Thanks.



-Dave





---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] Index lookup on and criteria

[A. Kretschmer]

am  01.11.2005, um 12:18:19 -0600 mailte David Durham folgendes:
 Apologies if this questions is asked often.  I'm doing some select 
 statements based on a timestamp field.  I have an index on the field, and 
 when I use the '=' operator the index is used.  However, if I use the '' 
 or '' operators, then it does a full table scan.  I've got around 6 
 million rows, so I would think that an index scan would be more 
 appropriate.
 
 
 Here are the statements I'm looking at:
 
 select * from myTable where myTimeStamp = '10/1/2005';
 
 uses an index.
 
 select max(myTimeStamp) from myTable;
 select * from myTable where myTimeStamp  '10/2/2005';
 select * from myTable where myTimeStamp  '10/2/2005' and myTimeStamp
 = '10/1/2005';
 
 do not use indexes.  Can anyone point me to some info about what's going 
 on?  I've started reading through the manual (chapter 13) which I think 

Nice question. My guess:

The planner fels its better to use seq-scan. My test:

,[  Test  ]
| Produktionsreport=# explain select * from bde_meldungen where zeitpunkt  
'2005-08-30'::timestamp;
|  QUERY PLAN
| 
|  Seq Scan on bde_meldungen  (cost=0.00..33862.46 rows=55106 width=139)
|Filter: (zeitpunkt  '2005-08-30 00:00:00'::timestamp without time zone)
| (2 Zeilen)
|
| Produktionsreport=# explain select * from bde_meldungen where zeitpunkt  
'2005-09-30'::timestamp;
| QUERY PLAN
| 
---
|  Index Scan using bde_zeitpunkt on bde_meldungen  (cost=0.00..8255.23 
rows=9521 width=139)
|Index Cond: (zeitpunkt  '2005-09-30 00:00:00'::timestamp without time 
zone)
| (2 Zeilen)
`

Sorry about the german column names, 'zeitpunkt' is a timestamp. On the
first query the result set is estimeted 55.000 rows long - seq-scan.
The second test: estimated to rows=9521 - index scan.


Btw.: min/max cant use index, this is coming with 8.1. I'm using for
examples above 7.4.6.


PS.: you can use set ..., example:

,[  Test with set enable_seqscan=... ]
| Produktionsreport=# set enable_seqscan=on;
| SET
| Produktionsreport=# explain analyse select * from bde_meldungen where 
zeitpunkt  '2005-08-30'::timestamp;
| QUERY PLAN
| 
---
|  Seq Scan on bde_meldungen  (cost=0.00..33862.46 rows=55106 width=139) 
(actual time=2574.004..4892.563 rows=99915 loops=1)
|Filter: (zeitpunkt  '2005-08-30 00:00:00'::timestamp without time zone)
|  Total runtime: 4971.179 ms
| (3 Zeilen)
|
| Produktionsreport=# set enable_seqscan=off;
| SET
| Produktionsreport=# explain analyse select * from bde_meldungen where 
zeitpunkt  '2005-08-30'::timestamp;
|   QUERY PLAN
| 
---
|  Index Scan using bde_zeitpunkt on bde_meldungen  (cost=0.00..47679.39 
rows=55106 width=139) (actual time=57.387..1649.591 rows=99915 loops=1)
|Index Cond: (zeitpunkt  '2005-08-30 00:00:00'::timestamp without time 
zone)
|  Total runtime: 1729.420 ms
| (3 Zeilen)
`


Now it using the index _and_ it is faster!


HTH, Andreas
-- 
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz:  035242/47212,  D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===Schollglas Unternehmensgruppe=== 

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] Index lookup on and criteria

[Michael Fuhr]

On Tue, Nov 01, 2005 at 12:18:19PM -0600, David Durham wrote:
 Apologies if this questions is asked often.  I'm doing some select 
 statements based on a timestamp field.  I have an index on the field, 
 and when I use the '=' operator the index is used.  However, if I use 
 the '' or '' operators, then it does a full table scan.  I've got 
 around 6 million rows, so I would think that an index scan would be more 
 appropriate.

No need to guess: run the queries with enable_seqscan disabled and
see if an index scan is indeed faster.

select max(myTimeStamp) from myTable;

In current releases min() and max() can't use indexes; search the
archives for numerous discussions of the reasons.  The workarounds
are, respectively:

SELECT myTimeStamp FROM myTable ORDER BY myTimeStamp LIMIT 1;
SELECT myTimeStamp FROM myTable ORDER BY myTimeStamp DESC LIMIT 1;

In 8.1 min() and max() are optimized to do the above.

select * from myTable where myTimeStamp  '10/2/2005';
select * from myTable where myTimeStamp  '10/2/2005' and myTimeStamp
= '10/1/2005';

How many rows do these queries return?  If they return a significant
portion of the table then the planner might think that a sequential
scan would be faster than an index scan.  It would be useful to see
the EXPLAIN ANALYZE output of these queries so we can see how
accurate the planner's row count estimates are.

Has the table been vacuumed and analyzed?  If so, and if the planner's
row count estimates aren't close to the actual row counts, then you
might benefit from increasing the statistics target for the myTimeStamp
column.

How much memory do you have and what's your effective_cache_size
setting?  That's one of the settings that influences the planner's
decision.  Also, what version of PostgreSQL are you running?

BTW, pgsql-performance would be a more appropriate list to discuss
performance issues.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[SQL] PGSQL encryption functions

[Mark R. Dingee]

Everyone,

I'm in need of a one-way pgsql script that will take a plain-text string and return an ecrypted string (preferably 32 character) .  I've been using md5('string'), but I'm concerned it's too weak for my needs.  Does anyone have any recommendations?

Thanks,
Mark

Re: [SQL] PGSQL encryption functions

[Josh Berkus]

Mark,

 I'm in need of a one-way pgsql script that will take a plain-text string
 and return an ecrypted string (preferably 32 character) .  I've been
 using md5('string'), but I'm concerned it's too weak for my needs.  Does
 anyone have any recommendations?

You check out pgcrypto in /contrib in the PostgreSQL source?

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Index lookup on and criteria

[David Durham]

Michael Fuhr wrote:

No need to guess: run the queries with enable_seqscan disabled and
see if an index scan is indeed faster.



  select max(myTimeStamp) from myTable;



In current releases min() and max() can't use indexes; search the
archives for numerous discussions of the reasons.  The workarounds
are, respectively:


Good to know.  Thanks all.


How many rows do these queries return?  If they return a significant
portion of the table then the planner might think that a sequential
scan would be faster than an index scan.  It would be useful to see
the EXPLAIN ANALYZE output of these queries so we can see how
accurate the planner's row count estimates are.


Ok.  Looks like you guys caught me in a(n unintentional) lie.  Here goes:

sipcdr=# explain analyze select * from october_cdr_call where begin_time 
= '10/1/2005' and begin_time  '10/4/2005';


 QUERY PLAN 



--
 Index Scan using october_begin_time on october_cdr_call 
(cost=0.00..98383.82 r

ows=24594 width=568) (actual time=0.280..79274.579 rows=538592 loops=1)
   Index Cond: ((begin_time = '2005-10-01 00:00:00'::timestamp without 
time zon

e) AND (begin_time  '2005-10-04 00:00:00'::timestamp without time zone))
 Total runtime: 81457.938 ms
(3 rows)

sipcdr=# explain analyze select * from october_cdr_call where begin_time 
 '10/15/2005';

   QUERY PLAN

--
 Seq Scan on october_cdr_call  (cost=0.00..273437.39 rows=1639584 
width=568) (ac

tual time=11.623..43681.396 rows=2609215 loops=1)
   Filter: (begin_time  '2005-10-15 00:00:00'::timestamp without time 
zone)

 Total runtime: 54366.944 ms
(3 rows)




Has the table been vacuumed and analyzed?  


Brand new table that I haven't deleted anything from yet.



If so, and if the planner's
row count estimates aren't close to the actual row counts, then you
might benefit from increasing the statistics target for the myTimeStamp
column.


Ok, this is something that balances what might lead to overuse of the 
vacuum command?  I can just look that one up.




How much memory do you have and what's your effective_cache_size
setting?  


1.5 gig RAM, effective_cache_size is the default, so 1000.



That's one of the settings that influences the planner's
decision.  Also, what version of PostgreSQL are you running?


8.0.3



BTW, pgsql-performance would be a more appropriate list to discuss
performance issues.


Ok, I won't cross post this one, but I'll send the next one there.

Here's the final word on this, I think:

sipcdr=# set enable_seqscan=off;
SET
sipcdr=# explain analyze select * from october_cdr_call where begin_time 
 '10/15/2005';


   QUERY PLAN 


---
 Index Scan using october_begin_time on october_cdr_call 
(cost=0.00..6338044.65 rows=1639584 width=568) (actual 
time=51.454..355782.687 rows=2609215 loops=1)
   Index Cond: (begin_time  '2005-10-15 00:00:00'::timestamp without 
time zone)

 Total runtime: 366289.918 ms


Thanks again,

-Dave

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] PGSQL encryption functions

[Bruno Wolff III]

On Tue, Nov 01, 2005 at 14:38:05 -0500,
  Mark R. Dingee [EMAIL PROTECTED] wrote:
 Everyone,
 
 I'm in need of a one-way pgsql script that will take a plain-text string and 
 return an ecrypted string (preferably 32 character) .  I've been using 
 md5('string'), but I'm concerned it's too weak for my needs.  Does anyone 
 have any recommendations?

What are your needs?

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] PGSQL encryption functions

[Mark R. Dingee]

I'll check it out.  Thanks, Josh

On Tuesday 01 November 2005 02:49 pm, Josh Berkus wrote:
 Mark,

  I'm in need of a one-way pgsql script that will take a plain-text string
  and return an ecrypted string (preferably 32 character) .  I've been
  using md5('string'), but I'm concerned it's too weak for my needs.  Does
  anyone have any recommendations?

 You check out pgcrypto in /contrib in the PostgreSQL source?



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[SQL] Can't Get SETOF Function to Work

[Lane Van Ingen]

Have tried perhaps 20+ alternatives on this plpgsql function, but can't get
it to work. Suspect I have done something wrong either with RETURN values,
creating of a type, or (most likely) use of ROW().

I am trying to fix the returned rows from enclosing reults in parenthesis;
found a similar incident in archives, but there is not enough detail to help
me figure out what is wrong:
  http://archives.postgresql.org/pgsql-sql/2005-10/threads.php#00321

Can someone spot what I am doing wrong? Here is what I have:

CREATE TYPE typ_history_rec_format AS
( interface integer,
  updatedTime timestamp(3),
  rftype integer,
  rfspeed bigint) ;

CREATE OR REPLACE FUNCTION router_history()
  RETURNS SETOF typ_history_rec_format AS
$BODY$
DECLARE
  returnValue   RECORD;
  workarea  RECORD;
  work_interfaceinteger;
  work_rftype   integer;
BEGIN
  FOR workarea IN
select '1' AS seq, if_id AS interface, updated_time AS updatedTime,
  link_type AS rftype, 0 AS rfspeed
  FROM rf_type_history
union
select '2' AS seq, if_id AS interface, updated_time AS updatedTime,
  0 AS rftype, speed AS rfspeed
  FROM rf_speed_history
order by 2,3,1  LOOP
if workarea.seq = 1 then
  work_interface := workarea.interface;
  work_rftype := workarea.rftype;
else
  if workarea.interface = work_interface then
select into returnValue ROW(workarea.interface,workarea.updatedTime,
  work_rftype,workarea.rfspeed);
RETURN NEXT returnValue;
  end if;
end if;
  END LOOP;
  RETURN;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

WHAT GETS RETURNED:
Query: select * from router_history();
Result:
  ERROR:  wrong record type supplied in RETURN NEXT
CONTEXT:  PL/pgSQL function router_history line 29 at return next



---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] PGSQL encryption functions

[Mark R. Dingee]

Bruno,

I use an authenticate() function as a part of state maintenance in a PHP web 
app.  In the function, I generate an encrypted token that is then used in the 
validation process on subsequent pages.  md5 works, but I've been able to 
brute-force crack it very quickly, so I'm looking for an alternative.  Any 
thoughts would be greatly appreciated.

Thanks,
Mark

On Tuesday 01 November 2005 04:28 pm, Bruno Wolff III wrote:
 On Tue, Nov 01, 2005 at 14:38:05 -0500,

   Mark R. Dingee [EMAIL PROTECTED] wrote:
  Everyone,
 
  I'm in need of a one-way pgsql script that will take a plain-text string
  and return an ecrypted string (preferably 32 character) .  I've been
  using md5('string'), but I'm concerned it's too weak for my needs.  Does
  anyone have any recommendations?

 What are your needs?

 ---(end of broadcast)---
 TIP 4: Have you searched our list archives?

http://archives.postgresql.org

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] PGSQL encryption functions

[Tom Lane]

Mark R. Dingee [EMAIL PROTECTED] writes:
 md5 works, but I've been able to 
 brute-force crack it very quickly,

Really?  Where's your publication of this remarkable breakthrough?

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [SQL] Nested Table in PostgreSQL or some alternative Variants

[Jim C. Nasby]

What do you mean by 'nested table'? Maybe arrays will do what you want?

Typically (and this applies to other databases as well), this is done
using two tables and refferential integrity. IE:

CREATE TABLE purchase_order(
po_id   serial  CONSTRAINT purchase_order__po_id PRIMARY KEY
, customer_id   int CONSTRAINT purchase_order__customer_RI REFERENCES 
customer(id)
, more fields...
)

CREATE TABLE po_lines (
po_id   int CONSTRAINT po_lines__po_id_RI REFERENCES 
purchase_order(po_id)
, line_number   smallintNOT NULL
, ...
, CONSTRAINT po_lines__po_id_line_number PRIMARY KEY( po_id, line_number)
)

On Mon, Oct 31, 2005 at 02:22:05PM +0100, Thomas Zuberbuehler wrote:
 Hello there
 
 I've a problem. I can't find some information about nested tables in 
 PostgreSQL. Is this Features possible in pgsql or not?
 
 * When yes, how i can use and create nested tables with pgsql?
 * When no, which alternative are there (for same problem definition)?
 
 Thank you for help.
 Greetings from Zurich, Switzerland.
 Thomas Zuberbuehler
 
 ---(end of broadcast)---
 TIP 2: Don't 'kill -9' the postmaster
 

-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [SQL] Can't Get SETOF Function to Work

[Michael Fuhr]

On Tue, Nov 01, 2005 at 04:38:12PM -0500, Lane Van Ingen wrote:
 Have tried perhaps 20+ alternatives on this plpgsql function, but can't get
 it to work. Suspect I have done something wrong either with RETURN values,
 creating of a type, or (most likely) use of ROW().
 ...
 CREATE OR REPLACE FUNCTION router_history()
   RETURNS SETOF typ_history_rec_format AS
 $BODY$
 DECLARE
   returnValue RECORD;

Try declaring returnValue as typ_history_rec_format instead of RECORD.

 select into returnValue ROW(workarea.interface,workarea.updatedTime,
   work_rftype,workarea.rfspeed);

Get rid of the record constructor:

select into returnValue workarea.interface, workarea.updatedTime,
work_rftype, workarea.rfspeed;

Using your code with the above two changes and some canned data, I
got the function to work (where work means it ran and returned
some rows -- I didn't look closely at the logic to see if the results
were actually correct ;-).

-- 
Michael Fuhr

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Index lookup on and criteria

[Michael Fuhr]

On Tue, Nov 01, 2005 at 03:21:21PM -0600, David Durham wrote:
 sipcdr=# explain analyze select * from october_cdr_call where begin_time 
 = '10/1/2005' and begin_time  '10/4/2005';
 
  QUERY PLAN 
 
 
 --
  Index Scan using october_begin_time on october_cdr_call 
 (cost=0.00..98383.82 r
 ows=24594 width=568) (actual time=0.280..79274.579 rows=538592 loops=1)
Index Cond: ((begin_time = '2005-10-01 00:00:00'::timestamp without 
 time zon
 e) AND (begin_time  '2005-10-04 00:00:00'::timestamp without time zone))
  Total runtime: 81457.938 ms
 (3 rows)

The estimated row count (24594) is much different than the actual
row count (538592), which makes me wonder if the statistics are up
to date.  Try running ANALYZE on the table and then see if the
estimate is more accurate.  With a more accurate estimate the planner
might choose a sequential scan, but the other queries you posted
suggest that a sequential scan is indeed faster when you're fetching
this much data.

 Has the table been vacuumed and analyzed?  
 
 Brand new table that I haven't deleted anything from yet.

The table should still be analyzed to update the planner's statistics.
The planner uses statistics to estimate how many rows a query will
return, and that influences the choice of plan.

 How much memory do you have and what's your effective_cache_size
 setting?  
 
 1.5 gig RAM, effective_cache_size is the default, so 1000.

You'd probably benefit from raising effective_cache_size to reflect
the amount of memory being used for disk cache, both by PostgreSQL
and by the operating system; you might also benefit from adjusting
other settings like shared_buffers.  See a tuning guide like the
following for advice:

http://www.powerpostgresql.com/PerfList

-- 
Michael Fuhr

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] Can't Get SETOF Function to Work

[Lane Van Ingen]

It worked perfectly! Thank you so much for your help!

-Original Message-
From: Michael Fuhr [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 01, 2005 7:45 PM
To: Lane Van Ingen
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Can't Get SETOF Function to Work


On Tue, Nov 01, 2005 at 04:38:12PM -0500, Lane Van Ingen wrote:
 Have tried perhaps 20+ alternatives on this plpgsql function, but can't
get
 it to work. Suspect I have done something wrong either with RETURN values,
 creating of a type, or (most likely) use of ROW().
 ...
 CREATE OR REPLACE FUNCTION router_history()
   RETURNS SETOF typ_history_rec_format AS
 $BODY$
 DECLARE
   returnValue RECORD;

Try declaring returnValue as typ_history_rec_format instead of RECORD.

 select into returnValue
ROW(workarea.interface,workarea.updatedTime,
   work_rftype,workarea.rfspeed);

Get rid of the record constructor:

select into returnValue workarea.interface, workarea.updatedTime,
work_rftype, workarea.rfspeed;

Using your code with the above two changes and some canned data, I
got the function to work (where work means it ran and returned
some rows -- I didn't look closely at the logic to see if the results
were actually correct ;-).

--
Michael Fuhr



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq