plpgsql triggers in rules

is is possible for to have a "do instead" trigger on a view that is a
plpgsql function?

		
__________________________________________ 
Yahoo! DSL – Something to write home about. 
Just $16.99/mo. or less. 
dsl.yahoo.com 

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

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

session variables in 8.0

understand that in 8.0 pg has session variables.  cannot find in doc
how to use them.

		
__________________________________________ 
Yahoo! DSL – Something to write home about. 
Just $16.99/mo. or less. 
dsl.yahoo.com 

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

exceptions in rules

is there any way within a rule to raise an exception?

	
		
__________________________________ 
Yahoo! for Good - Make a difference this year. 
http://brand.yahoo.com/cybergivingweek2005/

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

               http://archives.postgresql.org

avg() with floating-point types

I have city and postal_code tables linked by city_postal_code through a
city_id and postal_code_id. The postal_codes have latitude/longitude,
the cities don't. I want to set the city lat/long to the average of the
associated postal codes (abstract for a minute on whether that actually
makes sense from a geographical perspective), so I have a statement:

update city set latitude = city2.lat from
  (select c.city_id, avg(pc.latitude) as lat
     from city c
       left join city_postal_code cpc using (city_id)
       left join postal_code pc using (postal_code_id)
     group by c.city_id) city2
where city2.city_id = city.city_id

The datatype of both city.latitude and postal_code.latitude is
number(16,12).

This works, but I would like to understand why there is sometimes a
discrepancy between avg(pc.latitude) and what actually gets inserted
into the city table -- is it the usual floating-point discrepancy or is
there something I can do about it? E.g. after the above update:

select c.latitude, avg(pc.latitude), c.latitude-avg(pc.latitude) as diff
  from city c
    left join city_postal_code cpc using (city_id)  
    left join postal_code pc using (postal_code_id)  
  group by c.city_id,c.latitude
  having avg(pc.latitude) != c.latitude

    latitude     |         avg         |        diff         

Re: avg() with floating-point types

"George Pavlov" <gpavlov <at> mynewplace.com> writes:
> The datatype of both city.latitude and postal_code.latitude is
> number(16,12).
> This works, but I would like to understand why there is sometimes a
> discrepancy between avg(pc.latitude) and what actually gets inserted
> into the city table -- is it the usual floating-point discrepancy or is
> there something I can do about it? E.g. after the above update:

You're forcing the result of the avg() calculation to be rounded to 12
digits when you store it into city.latitude.  Your example simply shows
that the avg() calculation is being done to more precision than that.

			regards, tom lane

---------------------------(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: avg() with floating-point types

On Sun, Jan 01, 2006 at 04:40:18PM -0800, George Pavlov wrote:
> The datatype of both city.latitude and postal_code.latitude is
> number(16,12).
> 
> This works, but I would like to understand why there is sometimes a
> discrepancy between avg(pc.latitude) and what actually gets inserted
> into the city table -- is it the usual floating-point discrepancy or is
> there something I can do about it? E.g. after the above update:

You've declared the numeric column as (16,12) so the inserted values
are rounded to the 12th decimal place.  Notice that that's where the
values start to differ:

>     latitude     |         avg         |        diff         
> -----------------+---------------------+---------------------
>  36.709374333333 | 36.7093743333333333 | -0.0000000000003333

Is such a difference significant to your application?  The distance
discrepancy is on the order of tens of nanometers, which seems
absurdly precise.  With lat/lon coordinates five places after the
decimal point is about meter-precision, which is probably more than
adequate for whatever you're doing.

Incidentally, if you're doing anything geospatial then you might
want to check out PostGIS:

http://www.postgis.org/

If you look around you should be able to find shapefiles with points
or polygons for cities and postal codes.

PostgreSQL and uuid/guid

Hello all.

While browsing the web I've seen that many people 'need' the ability to
create uuid/guid values from within the PostgreSQL. Most of them are
switching from MSSQL, and they're missing the newid() function, which in
MSSQL created the uuid/guid value.

Now I'm in need of such function too. I'm finaly putting together
MSSQL-like-merge-replication for PostgreSQL, and the guid/uuid values
would come more than handy here.

Let's pretend for a moment here that I'm not satisfied with any other
solution (for instance, assigning IDs for each replication 'subscriber',
and then combining that ID with sequence generated numbers to have
unique ID's within the replication system), and that I realy need
guid/uids.

I've found source code for the guid/uuid.  There is a program, called
uuidgen, wich creates the uuid. uuidgen is part of the libuuid1 package
from Debian/GNU. The sources are in the e2fsprogs pacgages, where I
found the source for the uuidgen.

Now, it should be possible to create postgresql function (in python,
forn instance) wich would call the uuidgen program, but that approach
seems rather slow.

I'd like to have postgresql function written in C that would call
uuid_generate
(http://www.die.net/doc/linux/man/man3/uuid_generate.3.html).
Considering what is said for the uuidgen manpage (concerning random and

delete from a using b in postgres 8.1.

Re: delete from a using b in postgres 8.1.

On 1/2/06, Peter Childs <peterachilds <at> gmail.com> wrote:
> a command such as
>
> delete from a using b where a.one=b.one and b.two='foo';
>
> works fine in 8.1 but fails totally in 8.0 however
>
> delete from a where a.one=b.one and b.two='foo';
>
> works fine in 8.0 and bellow but fails in 8.1 unless you have the
> add_missing_from option set to true in 8.1
> Even then it produces a nasty warning.
>

it will not break anything because you can set this flag in the
VERSION/PLATFORM COMPATIBILITY section of postgresql.conf

> This can break tones of clients admittedly the work around is quick but not
> particularly neat. What do the other sql database do and is this just
> another inconstancy in SQL?
>
> I realise this was done to fix a bug but its a nasty sitting there that many
> client developers are going to miss it before it goes back to the end user.
> I also don't think  this has been made particularly clear in changes.
>
> Peter Childs
>

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

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

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

getting a query column to return 0 if nothing matches