Re: how to create data on the fly?

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Bartosz Dmytrak
Sent: Tuesday, February 28, 2012 3:33 PM
To: bboett(at)free(dot)fr
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] how to create data on the fly?

Hi,

what is the mathematical definition of this sequence?

This could be done using plpgsql, but I have to know how to calculate values in the future.

Regards,
Bartek

2012/2/28 <bboett(at)free(dot)fr>

Hello!

i am again struggling with a problem i am unsure how to set up. I could
easily solve all in the php backend, but this would impede further extensions
and doesn't satisfy my curiosity :D

so, here's the problem: a patient takes everyday a medecine, and from time to
time comes in a result of a blood-sample.

Now i first of all, i want to draw a graph showing the dosis taken, and the
blood-values mesured. Later on i want to calculate the assimilation rate, the
saturation rate, and the effective rate, both in real, and prognosis....

My actual problem beeing that the dosis may be not simply 1 to n pills per day,
but for example day 1 1, day 2 1.25, day 3 .5, day 4 1, etc... the most
complex case to now i, has a period over 4 days....

so i set up:

CREATE TABLE patients (
id integer NOT NULL,
name text,
minzone real,
maxzone real,
refresh integer
);

CREATE TABLE inrdata (
id integer NOT NULL,
temps timestamp without time zone,
patid integer,
inr real
);

CREATE TABLE posologie (
id integer NOT NULL,
inrid integer,
champ text,
definition text,
valeur real
);

In patients i have the persons name, and the boundaries that are wanted for
the blood-sample value, in inrdata i have the timepoints where i get a
blood-sample-result, and i eventually adjust the posology.

in posologie i have the table at a timepoint i have stored at the moment in
champ=sequence, definition="1;1.25;0.5;1",

and actually i solve the problem with the frontend....

what i would like is to store the posology iterations in the posology table,
and be able to make a select that generates the data on the fly. Taking
the different waypoints given by the data in inrdata as starting point
and computing for each day the actual dosis....

but i have no idea how to do this in sql?

any help appreciated!

ciao Bruno
==============================================================
bboett(at)adlp(dot)org

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

A recent discussion comes to mind:

SELECT unnest(champ_array_col), generate_series(1, total_number_of_rows_desired) …

LIMIT total_number_of_rows_desired

This gets you “greatest-common-multiple” number of rows, with whatever values are part of the array repeating as necessary, which you then limit to your actual desired number of rows.

Not sure if this helps but it would at least let you store the periods (in the form on an explicit array) and generate the desired timeline over however many increments are needed.

You could also try:

(pseudo code) … generate_series(1, length(champ_array_col) * number_of_periods_desired)

This will always generate exactly length*period_count rows with each element of the array appearing every length rows and a total of “number_of_periods_desired” times.

HTH,

David J.