Re: a spatial table's bounding box

Re: Edit multiple rows concurrent save

On 2011-09-29, Péter Szabó <matyee.nmi <at> gmail.com> wrote:

> users_has_cards.auctions + users_has_cards.decks never can be higher
> then users_has_cards.total. It should be also granted that
> users_has_cards.total - users_has_cards.auctions number of cards from
> a specific type can be in any decks.
>
> The deck assembly input comes from web, and parsed with PHP, so it is
> absolutely not trusted.
>
> I am started to write a PL/PgSQL function to handle the save of a
> deck, but it seems unable to solve this issue. I don't know how to go
> forward, so any idea is appreciated.

so users_has_cards has a check ( total >= decks + auctions )

and decks_has_cards  needs triggers on insert, update, and delete that 
manipulate the decks column of the users_has_cards record that
corresponds to that card.

This is probably goiung to make updates to the decks_has_cards table about 
10 times slower (should still be faster than you could have done in PHP)

-- 
⚂⚃ 100% natural

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

plpgsql function executed multiple times for each return value

We have a plpgsql function called paymentcalc, which calculates the 
payment necessary to pay off a loan.  It's defined like this:

CREATE OR REPLACE FUNCTION paymentcalc(IN amount numeric, IN 
interestrate numeric, IN termmonths integer, IN paymentfreq integer, IN 
dueday1 integer, IN dueday2 integer, IN borrowdate date, IN firstdue 
date, IN gapins character, IN lifeins character, IN disabilityins 
character, OUT payment numeric, OUT finalpayment numeric, OUT finaldue date)
   RETURNS record AS
...
LANGUAGE 'plpgsql' STABLE;

We want to execute this function, with inputs from a table, and return 
the calculated values as separate columns:

select (p).payment, (p).finalpayment, (p).finaldue
from
	(select paymentcalc(amount, interestrate / 100, termmonths, 
paymentfreq, dueday1, dueday2,
	        borrowdate, firstdue, gapins, lifeins, disins) as p
	from apps
	where id = 100) s

This works, but using "RAISE NOTICE" we've determined that this executes 
the paymentcalc function 3 times!  It seems to execute the function once 
for each return value.  The function is time consuming, and we want to 
execute it only once.

Thinking that maybe a table returning function would work better, we 
tried this:

CREATE OR REPLACE FUNCTION paymentcalc2(IN amount numeric, IN 
interestrate numeric, IN termmonths integer, IN paymentfreq integer, IN 
dueday1 integer, IN dueday2 integer, IN borrowdate date, IN firstdue 
date, IN gapins character, IN lifeins character, IN disabilityins character)
   RETURNS SETOF paymentcalc_return AS
...
LANGUAGE 'plpgsql' STABLE;

However, we now have difficulty trying to send parameters from a table 
as inputs to this function.  We want to do something like:

select *
from paymentcalc2(amount, interestrate / 100, termmonths, paymentfreq, 
dueday1, dueday2,
	        borrowdate, firstdue, gapins, lifeins, disins)
from apps
where id = 100

But obviously this query won't work with two "from"s.  We can't do a 
join between paymentcalc2 and apps, because we get "invalid reference to 
FROM-clause entry for table apps".

So my questions are:  1) How do we cause the paymentcalc function to be 
executed only once?  and 2) How do we call a table returning function 
with inputs from a table?

Thank you very much!

Steve

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: plpgsql function executed multiple times for each return value

Re: plpgsql function executed multiple times for each return value

Steve Northamer <stevenorthamer <at> gmail.com> writes:
> So my questions are:  1) How do we cause the paymentcalc function to be 
> executed only once?

In recent versions, I think marking it volatile would be sufficient.

			regards, tom lane

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[GENERAL] Creating rule for sliding data

Hello,

I have a problem to find the good syntax for a rule for rows going for 
one partition to the other in cas of an update.

Let me give the conditions :

1 - having a mother table

CREATE TABLE T_MESURE_MSR
(
   MSR_ID      INT   NOT NULL,
   MSR_DATE    DATE  NOT NULL,
   MSR_MESURE  FLOAT NOT NULL
);

2 - having 2 child table :

CREATE TABLE T_MESURE_BEFORE2000_MSR
(
CHECK ( MSR_DATE < DATE '2000-01-01')
) INHERITS (T_MESURE_MSR)

CREATE TABLE T_MESURE_AFTER1999_MSR
(
CHECK ( MSR_DATE >= DATE '2000-01-01')
) INHERITS (T_MESURE_MSR)

THE QUESTION...

How to make the proper rule for T_MESURE_MSR il a rox goes from 1998 to 
2003 ?

This one does not work :

CREATE RULE R_U_MSR_BEFORE2000
AS
ON UPDATE TO T_MESURE_MSR
    WHERE ( NEW.MSR_DATE < DATE '2000-01-01' )
DO INSTEAD
-- rows does not change partition :
   UPDATE T_MESURE_BEFORE2000_MSR
   SET MSR_ID     = NEW.MSR_ID,
       MSR_DATE   = NEW.MSR_DATE,
       MSR_MESURE = NEW.MSR_MESURE
   WHERE ( OLD.MSR_DATE < DATE '2000-01-01' );

-- rows does change partition (first INSERT NEWs then DELETE OLDs)
   INSERT INTO T_MESURE_MSR
   VALUES ( NEW.MSR_ID,
            NEW.MSR_DATE,
            NEW.MSR_MESURE )
   WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' );

   DELETE FROM T_MESURE_MSR
   WHERE  MSR_ID = OLD.MSR_ID
     AND  MSR_DATE = OLD.MSR_DATE
     AND  MSR_MESURE = OLD.MSR_MESURE
   WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' );

And no more for this one :

CREATE RULE R_U_MSR_BEFORE2000
AS
ON UPDATE TO T_MESURE_MSR
    WHERE ( NEW.MSR_DATE < DATE '2000-01-01' )
DO INSTEAD
-- rows does not change partition :
   UPDATE T_MESURE_BEFORE2000_MSR
   SET MSR_ID     = NEW.MSR_ID,
       MSR_DATE   = NEW.MSR_DATE,
       MSR_MESURE = NEW.MSR_MESURE
   WHERE ( OLD.MSR_DATE < DATE '2000-01-01' );

-- rows does change partition (first INSERT the NEWs then DELETE the OLDs)
   INSERT INTO T_MESURE_MSR
   SELECT MSR_ID,
          MSR_DATE,
          MSR_MESURE
   FROM   NEW
   WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' );

   DELETE FROM T_MESURE_MSR
   WHERE  (MSR_ID, MSR_DATE, MSR_MESURE)
          IN (SELECT MSR_ID, MSR_DATE, MSR_MESURE
              FROM   OLD
              WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' ));

Any idea ?

Thanks

-- 
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR  :  http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [GENERAL] Creating rule for sliding data

I am answering to myseilf...

the good syntax is something like :

CREATE RULE R_U_MSR_BEFORE2000x
AS
ON UPDATE TO T_MESURE_MSR
    WHERE ( NEW.MSR_DATE < DATE '2000-01-01' )
DO INSTEAD
(
-- rows does not change partition :
   UPDATE T_MESURE_BEFORE2000_MSR
   SET MSR_ID     = NEW.MSR_ID,
       MSR_DATE   = NEW.MSR_DATE,
       MSR_MESURE = NEW.MSR_MESURE
   WHERE ( OLD.MSR_DATE < DATE '2000-01-01' );
-- rows does change partition (first INSERT NEWs then DELETE OLDs)
   INSERT INTO T_MESURE_MSR
   SELECT MSR_ID,
          MSR_DATE,
          MSR_MESURE
   FROM   NEW
   WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' ); ;
   DELETE FROM T_MESURE_MSR
   WHERE  MSR_ID = OLD.MSR_ID
     AND  MSR_DATE = OLD.MSR_DATE
     AND  MSR_MESURE = OLD.MSR_MESURE
     AND  NOT ( OLD.MSR_DATE < DATE '2000-01-01' );
);

The problem is nowhere in the doc there is a mention where much more 
than one commande must be place into brackets !

A +

-- 
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR  :  http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [GENERAL] Creating rule for sliding data

Hi,

On Sun, 2011-10-09 at 18:50 +0200, F. BROUARD / SQLpro wrote:
> I am answering to myseilf...
> 
> the good syntax is something like :
> 
> 
> CREATE RULE R_U_MSR_BEFORE2000x
> AS
> ON UPDATE TO T_MESURE_MSR
>     WHERE ( NEW.MSR_DATE < DATE '2000-01-01' )
> DO INSTEAD
> (
> -- rows does not change partition :
>    UPDATE T_MESURE_BEFORE2000_MSR
>    SET MSR_ID     = NEW.MSR_ID,
>        MSR_DATE   = NEW.MSR_DATE,
>        MSR_MESURE = NEW.MSR_MESURE
>    WHERE ( OLD.MSR_DATE < DATE '2000-01-01' );
> -- rows does change partition (first INSERT NEWs then DELETE OLDs)
>    INSERT INTO T_MESURE_MSR
>    SELECT MSR_ID,
>           MSR_DATE,
>           MSR_MESURE
>    FROM   NEW
>    WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' ); ;
>    DELETE FROM T_MESURE_MSR
>    WHERE  MSR_ID = OLD.MSR_ID
>      AND  MSR_DATE = OLD.MSR_DATE
>      AND  MSR_MESURE = OLD.MSR_MESURE
>      AND  NOT ( OLD.MSR_DATE < DATE '2000-01-01' );
> );
> 
> The problem is nowhere in the doc there is a mention where much more 
> than one commande must be place into brackets !
> 

As a matter of fact, it does:

CREATE [ OR REPLACE ] RULE name AS ON event
 TO table [ WHERE condition ]
 DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

                                             ^                       ^
                                             |                       |
See -----------------------------------------+------------------------

Extract from
http://www.postgresql.org/docs/9.1/interactive/sql-createrule.html

-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [GENERAL] Creating rule for sliding data

On Sun, 2011-10-09 at 21:17 +0200, F. BROUARD / SQLpro wrote:
> Hi,
> 
> Le 09/10/2011 19:07, Guillaume Lelarge a écrit :
> > Hi,
> >
> > On Sun, 2011-10-09 at 18:50 +0200, F. BROUARD / SQLpro wrote:
> >> I am answering to myseilf...
> >>
> >> the good syntax is something like :
> >>
> >>
> >> CREATE RULE R_U_MSR_BEFORE2000x
> >> AS
> >> ON UPDATE TO T_MESURE_MSR
> >>      WHERE ( NEW.MSR_DATE<  DATE '2000-01-01' )
> >> DO INSTEAD
> >> (
> >> -- rows does not change partition :
> >>     UPDATE T_MESURE_BEFORE2000_MSR
> >>     SET MSR_ID     = NEW.MSR_ID,
> >>         MSR_DATE   = NEW.MSR_DATE,
> >>         MSR_MESURE = NEW.MSR_MESURE
> >>     WHERE ( OLD.MSR_DATE<  DATE '2000-01-01' );
> >> -- rows does change partition (first INSERT NEWs then DELETE OLDs)
> >>     INSERT INTO T_MESURE_MSR
> >>     SELECT MSR_ID,
> >>            MSR_DATE,
> >>            MSR_MESURE
> >>     FROM   NEW
> >>     WHERE NOT ( OLD.MSR_DATE<  DATE '2000-01-01' ); ;
> >>     DELETE FROM T_MESURE_MSR
> >>     WHERE  MSR_ID = OLD.MSR_ID
> >>       AND  MSR_DATE = OLD.MSR_DATE
> >>       AND  MSR_MESURE = OLD.MSR_MESURE
> >>       AND  NOT ( OLD.MSR_DATE<  DATE '2000-01-01' );
> >> );
> >>
> >> The problem is nowhere in the doc there is a mention where much more
> >> than one commande must be place into brackets !
> >>
> >
> > As a matter of fact, it does:
> >
> > CREATE [ OR REPLACE ] RULE name AS ON event
> >   TO table [ WHERE condition ]
> >   DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }
> >
> >                                               ^                       ^
> >                                               |                       |
> > See -----------------------------------------+------------------------
> >
> > Extract from
> > http://www.postgresql.org/docs/9.1/interactive/sql-createrule.html
> >
> >
> 
> Please give a real example instead of copying the doc that I have read a 
> lot !
> 
> I am not so stupid to have post this topic without having try many 
> syntaxes wich does not works !
> 

I don't think you're stupid. You said the doc was wrong, and I answered
you it wasn't. But I understand it didn't help you solve your issue...

Anyway, if you gave us the error message, it would be easier to answer
you. Here is the error message I get:

ERROR:  relation "new" does not exist
LINE 18:    FROM   NEW
                   ^

And actually, you can't use "FROM NEW". And this:

  INSERT INTO T_MESURE_MSR
  SELECT MSR_ID,
  MSR_DATE,
  MSR_MESURE
  FROM   NEW
  WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' )

has no meaning at all in PostgreSQL.

-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re:

..Change your life right now!  http://www.liscen.com/com.friend.php?fsid=76de6

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql