Skip site navigation (1) Skip section navigation (2)

Re: how to create data on the fly?

From: "David Johnston" <polobo(at)yahoo(dot)com>
To: "'Bartosz Dmytrak'" <bdmytrak(at)eranet(dot)pl>, <bboett(at)free(dot)fr>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: how to create data on the fly?
Date: 2012-02-28 20:44:40
Message-ID: 00c301ccf659$cc045520$640cff60$ (view raw, whole thread or download thread mbox)
Lists: pgsql-general

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?



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.



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


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

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



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.




David J.


In response to

pgsql-general by date

Next:From: Rich ShepardDate: 2012-02-28 20:46:38
Subject: Re: what Linux to run
Previous:From: Clodoaldo NetoDate: 2012-02-28 20:39:12
Subject: Error installing plpythonu in 9.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group