[SQL] How to prevent recursion
Hi,
I try to save hierarchical Data with an
id that contains strings which represent
the way through the tree. On each depth level
I can have a maxcount number of elements.
If I want move subtrees, I have to change these
id-fields. I want implement this as a RULE in the
Database, that the using application not have to
call any SQL statements in a sequence.
I tried:
CREATE RULE hangup AS
ON UPDATE TO "products"
WHERE OLD."id" != NEW."id"
DO INSTEAD
UPDATE "products" SET "id"=NEW."id" ||
substring("id",char_length(OLD."id")+1) WHERE "id" LIKE (OLD."id" || '~')
That runs definitely in recursion, and I have no idea to prevent
this. Perhaps it runs with triggers and/or Functions.
Thank for your help
Falk
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] bulk imports with sequence
I am importing a large number of records monthly using the \copy (from text file)command. I would like to use a sequence as a unique row ID for display in my app. Is there any way to let postgresql generate the sequence itself. Currently the only way I can make it work is to grab the next seq value and insert my own numbers into the file Thank You, -Aaron Held ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] bulk imports with sequence
On 20 Aug 2002 at 7:55, Aaron Held wrote:
> I am importing a large number of records monthly using the \copy (from
> text file)command.
>
> I would like to use a sequence as a unique row ID for display in my
> app.
>
> Is there any way to let postgresql generate the sequence itself.
> Currently the only way I can make it work is to grab the next seq
> value and insert my own numbers into the file
Yes:
create sequence mytable_id_seq;
alter table mytable alter column id set default
nextval('mycolumn_id_seq'::text);
--
Dan Langille
I'm looking for a computer job:
http://www.freebsddiary.org/dan_langille.php
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Event recurrence - in database or in application code ????
One of the features that I am attempting to implement in the system that I am building is the capability to schedule events (with recurrence). My question to those of you that are more experienced in postgresql is whether you would implement this functionality in the database level using triggers or at the application code level (PHP). Ideally I would like to be able to generate an event off a template appointment (initial appt) and have it schedule identical appointments hourly, daily, weekly, monthly, or by selectable range (ie: first tuesday every month). I would also like to have the capability to remove an appointment and leave others (much like using C pointers - I think)or to remove all (cascading delete). Any suggestions, etc gratefully appreciated. Darrin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] sql subqueries problem
On Tue, 20 Aug 2002, Mathieu Arnold wrote:
> --On lundi 19 août 2002 09:45 -0700 Stephan Szabo
> <[EMAIL PROTECTED]> wrote:
>
> >
> > On Mon, 19 Aug 2002, Mathieu Arnold wrote:
> >
> >> Hi
> >>
> >> I have my accounting in a database, and I have a problem with subqueries,
> >> here is what I have :
> >>
> >>
> >>
> >> SELECT f.numero,
> >> f.id_client,
> >> f.date_creation,
> >> (f.date_creation + (f.echeance_paiement||'
> >> days')::interval)::date AS echeance,
> >> f.montant_ttc,
> >> ROUND(CASE WHEN remise IS NULL THEN 0 ELSE remise END - CASE
> >> WHEN facture IS NULL THEN 0 ELSE facture END,2) AS solde,
> >> CASE WHEN (f.date_creation + (f.echeance_paiement||'
> >> days')::interval)::date < 'now'::date
> >>THEN round(f.montant_ttc * 10 / 100 * ('now'::date -
> >> (f.date_creation + (f.echeance_paiement||' days')::interval)::date)::int
> >> / 365, 2)
> >>ELSE NULL
> >> END AS penalite
> >> FROM facture AS f
> >>JOIN (SELECT ff.id_client,
> >> SUM(ff.montant_ttc / df.taux) AS facture
> >> FROM facture AS ff
> >> JOIN devise AS df USING (id_devise)
> >> GROUP BY ff.id_client
> >> ) AS fff USING (id_client)
> >>LEFT OUTER JOIN (SELECT rr.id_client,
> >> SUM(rr.montant / dr.taux) AS remise
> >> FROM remise AS rr
> >>JOIN devise AS dr USING (id_devise)
> >> GROUP BY rr.id_client
> >>) AS rrr USING (id_client)
> >> WHERE ROUND(CASE WHEN remise IS NULL THEN 0 ELSE remise END - CASE WHEN
> >> facture IS NULL THEN 0 ELSE facture END,2) < 0
> >> GROUP BY f.numero, f.date_creation, f.date_creation +
> >> (f.echeance_paiement||' days')::interval, f.id_client, f.montant_ttc,
> >> rrr.remise, fff.facture
> >> ORDER BY f.id_client, f.numero
> >>
> >> Table "facture"
> >> Column | Type
> >> ---+---
> >> id_facture| integer
> >> date_creation | date
> >> date_modif| date
> >> echeance_paiement | integer
> >> id_client | integer
> >> id_devise | integer
> >> genere| integer
> >> montant_ht| double precision
> >> montant_tva | double precision
> >> montant_ttc | double precision
> >> solde_anterieur | double precision
> >> total_a_payer | double precision
> >> numero| character varying(15)
> >> ref | character varying(60)
> >> responsable | character varying(60)
> >> contact | character varying(60)
> >> num_tva | character varying(60)
> >> adresse | text
> >> pied | text
> >> commentaire | text
> >> email | text
> >> Table "remise"
> >> Column | Type
> >> +--
> >> id_remise | integer
> >> date_paiement | date
> >> date_remise| date
> >> id_client | integer
> >> id_type_remise | integer
> >> id_devise | integer
> >> id_banque | integer
> >> montant| double precision
> >> commentaire| text
> >> Table "devise"
> >> Column | Type
> >> ---+---
> >> id_devise | integer
> >> taux | double precision
> >> devise| character varying(30)
> >> symbole | character varying(15)
> >>
> >> It finds the invoices (facture) from my customers who forgot to pay me.
> >> but, the probem is that it gives me all the invoices and not only the
> >> ones which are not paid, so, I wanted to add something like :
> >> WHERE ff.date_creation <= f.date_creation
> >> in the first subselect, and
> >> WHERE rr.date_paiement <= f.date_creation
> >> in the second subselect, but I can't because postgresql does not seem to
> >> be able to do it. Any idea ?
> >
> > I don't think f is in scope on those subqueries.
> > Can you put the clauses on the outer where or as part of the
> > join conditions?
> >
>
> I've tried, but, as the subselect is an aggregate, I can't get it (maybe I
> don't know enough about it to do it :)
Right, that'd make it harder. :)
Hmm, would something like:
FROM
(select *,
(select sum(ff.montant_ttc/df.taux) from facture ff join
devise as df using (id_devise) where ff.date_creation <=
f.date_creation and ff.id_client=f.id_client
group by ff.id_client) as facture,
(select sum(rr.montant/dr.taux) from remise as rr join
devise as dr using (id_devise) where rr.date_paiement <=
f.date_creation and rr.id_client=f.id_client
group by rr.id_client) as remise
From facture f
);
give you something closer to what you want?
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] bulk imports with sequence
"Aaron Held" <[EMAIL PROTECTED]> writes: > I am importing a large number of records monthly using the \copy (from text >file)command. > I would like to use a sequence as a unique row ID for display in my app. > Is there any way to let postgresql generate the sequence itself. Currently the only >way I > can make it work is to grab the next seq value and insert my own numbers into the >file Right now the only reasonable way to do that is to do the \copy into a temporary table (that's missing the sequence column) and then do insert into realtable(column list) select * from temptable; where the column list lists the columns you're pulling from the temp table. The INSERT will substitute the default value (viz, nextval()) in the sequence column. In 7.3 it'll be possible to do this in one step with no temp table: COPY will accept a column list, so you can get the same effect just with COPY. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] sql subqueries problem
>> I've tried, but, as the subselect is an aggregate, I can't get it (maybe >> I don't know enough about it to do it :) > > Right, that'd make it harder. :) > > Hmm, would something like: > > FROM > (select *, > (select sum(ff.montant_ttc/df.taux) from facture ff join > devise as df using (id_devise) where ff.date_creation <= > f.date_creation and ff.id_client=f.id_client > group by ff.id_client) as facture, > (select sum(rr.montant/dr.taux) from remise as rr join > devise as dr using (id_devise) where rr.date_paiement <= > f.date_creation and rr.id_client=f.id_client > group by rr.id_client) as remise > From facture f > ); > > give you something closer to what you want? that's exactly it, I guess that I was not twisted enough for that one :) -- Mathieu Arnold ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Problem with timestamp field/time function.. (upgrading from 7.0 to 7.2.1)
Hi All: I've googling around, searching the mailinglist archive and reading FAQ's but I haven't find the answer for my question. And I know it is quite commom! I'm trying upgrading to 7.2.1. I'm running postgresql 7.0 with a column like: Table "materia" Column | Type | Modifiers +--+--- materiadata| timestamp with time zone | not null mmateriatitulo | character varying(80)| not null materiasequencial | numeric(30,6)| not null I used to execute this query: select max(time(materiadata)) from materia; or select materiasequencial, materiatitulo, time(materiadata) from materia order by time(materiadata) desc; I've read at PostgreSQL 7.3dev Administrator Guide's Release Notes that time() and timestamp() functions in postgresql 7.2 are deprecated (so in 7.2.1). So, how can I get the same result above without using time() ?? Or if it not possible, how can I extract (yes, I tried with extract() function too) time from a timestamp column? I know it's quite simple question... but I haven't find any clue! Thanks a lot in advance. Bests regards []'s Lucas Brasilino [EMAIL PROTECTED] http://www.recife.pe.gov.br Emprel -Empresa Municipal de Informatica (pt_BR) Municipal Computing Enterprise (en_US) Recife - Pernambuco - Brasil Fone: +55-81-34167078 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Problem with timestamp field/time function.. (upgrading
On Tue, 20 Aug 2002, Lucas Brasilino wrote:
> I'm running postgresql 7.0 with a column like:
>
> Table "materia"
> Column | Type | Modifiers
> +--+---
>
> materiadata| timestamp with time zone | not null
> mmateriatitulo | character varying(80)| not null
> materiasequencial | numeric(30,6)| not null
>
>
> I used to execute this query:
>
> select max(time(materiadata)) from materia;
>
> or
> select materiasequencial, materiatitulo, time(materiadata)
> from materia
> order by time(materiadata) desc;
>
> I've read at PostgreSQL 7.3dev Administrator Guide's Release Notes that
> time() and timestamp() functions in postgresql 7.2 are deprecated (so in
> 7.2.1).
>
> So, how can I get the same result above without using time() ??
> Or if it not possible, how can I extract (yes, I tried with extract()
> function too) time from a timestamp column?
> I know it's quite simple question... but I haven't find any clue!
In general you could probably use CAST(materiadata as time) I'd guess.
I believe that at this point you can still use the functions, you just
need to double quote them ("time"(materiadata)) to differentiate them
from the type specifiers.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] functions and triggers
I'm trying to build a trigger that will update a timestamp field in a table with the current timestamp, and I just can't make it work. The problemas are two: 1) when I try to create the trigger, it says that the function doesn't exist. Why is this happening? 2) How does the trigger tell the function the row number identifier? I'm really stuck with this. Saludos... :-) -- Porqué usar una base de datos relacional cualquiera, si podés usar PostgreSQL? - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telematica Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 3: 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] functions and triggers
On Tue, 20 Aug 2002, Martin Marques wrote: > I'm trying to build a trigger that will update a timestamp field in a table with > the current timestamp, and I just can't make it work. > > The problemas are two: > > 1) when I try to create the trigger, it says that the function doesn't exist. > Why is this happening? You should probably show us what you were trying to do, but I'm going to guess that the function doesn't have the right signature. On current versions, Trigger functions should return opaque and take no arguments (any arguments given on the create trigger line are passed in a different fashion). > 2) How does the trigger tell the function the row number identifier? I'm not sure what you mean by this. Getting at the row being worked on depends somewhat on what language you're using. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] functions and triggers
Quoting Stephan Szabo <[EMAIL PROTECTED]>: > > > The problemas are two: > > > > 1) when I try to create the trigger, it says that the function doesn't > exist. > > Why is this happening? > > You should probably show us what you were trying to do, but I'm going to > guess that the function doesn't have the right signature. On > current versions, Trigger functions should return opaque and take no > arguments (any arguments given on the create trigger line are passed > in a different fashion). I have this function which works OK. CREATE FUNCTION ahora (integer) RETURNS integer AS ' UPDATE usuarios SET tmodif = now() WHERE codigo = $1; SELECT 1 as RESULT; ' LANGUAGE SQL > > 2) How does the trigger tell the function the row number identifier? > > I'm not sure what you mean by this. Getting at the row being worked on > depends somewhat on what language you're using. I thought about a simple SQL that does the update. You mean I just call the function from the trigger and thats all? -- Porqué usar una base de datos relacional cualquiera, si podés usar PostgreSQL? - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telematica Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] functions and triggers
On Tue, 20 Aug 2002, Martin Marques wrote: > Quoting Stephan Szabo <[EMAIL PROTECTED]>: > > > > > > The problemas are two: > > > > > > 1) when I try to create the trigger, it says that the function doesn't > > exist. > > > Why is this happening? > > > > You should probably show us what you were trying to do, but I'm going to > > guess that the function doesn't have the right signature. On > > current versions, Trigger functions should return opaque and take no > > arguments (any arguments given on the create trigger line are passed > > in a different fashion). > > I have this function which works OK. > > CREATE FUNCTION ahora (integer) RETURNS integer AS ' > UPDATE usuarios SET tmodif = now() > WHERE codigo = $1; > SELECT 1 as RESULT; > ' LANGUAGE SQL Trigger functions have no args and return opaque and I don't think you can use sql language functions, but I'm not sure. Something like: create function ahora_trigger() returns opaque as ' begin NEW.tmodif := now(); return NEW; end;' language 'plpgsql'; should work as a before update trigger. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Event recurrence - in database or in application code ????
Hello Darrin, I recently implemented what I would consider the "hard part" of a solution to this using Perl and Postgres. My solution handles multi-day events and recurring events, including events that are both multi-day and recurring. Here's an overview of how I did it: A table called "calendar" has just one column, "date". I inserted 10,000 rows into the table, one for every day starting a couple of years back and going _way_ into the the future. This is so that when I construct a SELECT statement to say "show me every day in May, 2002", I get back a row for every day, regardless of whether or not there was an event. A second table "events", holds my events including an event_id, and start and end dates and times. There is one row for each event, no matter if it recurs or is multi-day. A third table "events_calendar" is built based on the "events" table. In this table, a row is inserted for every day that an event occurs. So if an event spans 3 days and occurs a total of 3 times, there are 9 rows added to this table. For recurring events, the start and end dates and times are adjusted to be "local" to this occurance, not the original start date and time. In addition to the fields contained in the "events" table, the events_calendar table also has "date" column to denote which date is being refered to. Now with a simple SELECT statement that joins the calendar table with the events_calendar table, I can easily build a public view of the data with events appearing on as many dates as they should. On the administrative side, I have a few functions to make this work: - a function to build the entire events_calendar table initially - some functions to handle inserting events into events_calendar - some funcions to handle deleting events from events_calendar When I make an insert in the events table, I run the functions to create the inserts for the events_calendar. When I delete from the events table, the related rows from events_calendar table get deleted. When updating the events table, I delete from events_calendar, and then re-insert into it. I'm sure this piece could be done with triggers, but I'm much better at writing Perl, so I did it that way. :) I've been happy with this solution. I think the Perl turned out to be fairly easy to understand and maintain, the SQL that needs to be used ends up being fairly straightforward, and the performance is good because the selects to view the calendar are fairly simple. The one drawback is that sometime before 2028, I have to remember to add some rows to the calendar table. :) -mark http://mark.stosberg.com/ On Tue, 20 Aug 2002, Darrin Domoney wrote: > One of the features that I am attempting to implement in the system that I > am building is > the capability to schedule events (with recurrence). My question to those of > you that are > more experienced in postgresql is whether you would implement this > functionality in the > database level using triggers or at the application code level (PHP). > > Ideally I would like to be able to generate an event off a template > appointment (initial appt) > and have it schedule identical appointments hourly, daily, weekly, monthly, > or by selectable > range (ie: first tuesday every month). I would also like to have the > capability to remove an > appointment and leave others (much like using C pointers - I think)or to > remove all > (cascading delete). > > Any suggestions, etc gratefully appreciated. > > Darrin > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
