Re: [SQL] UPDATE Multiple Records At Once?

[lewbloch]

Carlos Mennens wrote:
> I'm trying to update a customer record in a table however I need to
> change several values (cust_address, cust_contact, cust_email). My
> question is how do I properly format this into one single command?
> 
> 
> forza=# SELECT cust_id, cust_name, cust_address, cust_contact, cust_email
> forza-# FROM customers
> forza-# WHERE cust_name = 'iamUNIX'
> forza-# ;
>   cust_id   | cust_name | cust_address  |  cust_contact  |
> cust_email
> +---+---++
>  16 | MobileNX   | 200 South Shore Drive | Carlos Mennens |
> carlos@xx**xx.com
> (1 row)
> 
> I did a quick Google search and I can see there is a method or
> procedure which involves parenthesis () however I'm not sure how I
> would change all the values listed above under one command. Can anyone
> please give me a quick example so I can see how this is drawn out via
> ANSI SQL?



-- 
Lew

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

Re: [SQL] Select row cells as new columns

[lewbloch]

danycxxx wrote:
> Yes, I've looked at it, but id doesn't create the desired output. After more
> research I've found that I my design is similar to Entity, Attribute and
> Value(EAV) design and I think I have to redesign. Any suggestion regarding
> EAV? Is there any other approach?

EAV is controversial. I am uncomfortable with it because it implements 
keys as values.

I suggest that you not use EAV. Its putative flexibility comes at a large 
implementation price.

The other approach is relational database design. You model an entity as 
a collection of tables, each of which represents an aspect of the data 
pertinent to the entity. Each table has columns, the labels of which 
correspond generally to the names of attributes for that aspect. Each 
row of each table provides values for its respective named columns.

So a table roughly models what I'll call an "entitylet" - a cohesive piece 
of the entity model.

The rules to decompose entity models into relational data models 
constitute "normalization". I suggest you create a relational data model 
normalized to at least third normal form.

-- 
Lew

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

Re: [SQL] Job control in sql

[lewbloch]

Svenne Krap wrote:
> On 29-05-2012 12:32, Ireneusz Pluta wrote:
> > W dniu 2012-05-25 10:28, Svenne Krap pisze:
> >> Hi.
> >>
> >> I am building a system, where we have jobs that run at different
> >> times (and takes widely different lengths of time).
> >>
> >> Basically I have a jobs table:
> >>
> >> create table jobs(
> >>  id serial,
> >> ready boolean,
> >> job_begun timestamptz,
> >> job_done timestamptz,
> >> primary key (id)
> >> );
> >>
> >> This should run by cron, at it is my intention that the cronjob
> >> (basically) consists of
> >> /
> >> psql -c "select run_jobs()"/
> >>
> >> My problem is, that the job should ensure that it is not running
> >> already, which would be to set job_begun when the job starts". That
> >> can easily happen as jobs should be started every 15 minutes (to
> >> lower latency from ready to done) but some jobs can run for hours..
> >>
> >> The problem is that a later run of run_jobs() will not see the
> >> job_begun has been set by a prior run (that is unfinished - as all
> >> queries from the plpgsql-function runs in a single, huge transaction).
> >>
> >> My intitial idea was to set the isolation level to "read uncommitted"
> >> while doing the is-somebody-else-running-lookup, but I cannot change
> >> that in the plpgsql function (it complains that the session has to be
> >> empty - even when I have run nothing before it).
> >>
> >> Any ideas on how to solve the issue?
> >>
> >> I run it on Pgsql 9.1.
> >>
> >> Svenne
> >
> > I think you might try in your run_jobs()
> > SELECT job_begun FROM jobs WHERE id = job_to_run FOR UPDATE NOWAIT;
> > This in case of conflict would throw the exception:
> > 55P03 could not obtain lock on row in relation "jobs"
> > and you handle it (or not, which might be OK too) in EXCEPTION block.
> >
> Hehe.. good idea...
> 
> In the mean time I had thought about using advisory locks for the same
> thing, but the old-fashioned locks work fine too.


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

Re: [SQL] Job control in sql

[lewbloch]

Sorry about the earlier unfinished post - premature click.

Svenne Krap wrote:
> Ireneusz Pluta wrote:
>> Svenne Krap pisze:
> >> I am building a system, where we have jobs that run at different
> >> times (and takes widely different lengths of time).
> >>
> >> Basically I have a jobs table:
> >>
> >> create table jobs(
> >>  id serial,
> >> ready boolean,
> >> job_begun timestamptz,
> >> job_done timestamptz,
> >> primary key (id)
> >> );
> >>
> >> This should run by cron, at it is my intention that the cronjob
> >> (basically) consists of
> >> /
> >> psql -c "select run_jobs()"/
> >>
> >> My problem is, that the job should ensure that it is not running
> >> already, which would be to set job_begun when the job starts". That
> >> can easily happen as jobs should be started every 15 minutes (to
> >> lower latency from ready to done) but some jobs can run for hours..
> >>
> >> The problem is that a later run of run_jobs() will not see the
> >> job_begun has been set by a prior run (that is unfinished - as all
> >> queries from the plpgsql-function runs in a single, huge transaction).
> >>
> >> My intitial idea was to set the isolation level to "read uncommitted"
> >> while doing the is-somebody-else-running-lookup, but I cannot change
> >> that in the plpgsql function (it complains that the session has to be
> >> empty - even when I have run nothing before it).
> >>
> >> Any ideas on how to solve the issue?

Use a database to hold data. Use run-time constructs in the program or 
script to handle run-time considerations.

How about using a shell script that uses "ps" to determine if a job is 
already running, or using a lock file in the file system known to the 
control script?

> >> I run it on Pgsql 9.1.
>>
>> I think you might try in your run_jobs()
>> SELECT job_begun FROM jobs WHERE id = job_to_run FOR UPDATE NOWAIT;
>> This in case of conflict would throw the exception:
>> 55P03 could not obtain lock on row in relation "jobs"
>> and you handle it (or not, which might be OK too) in EXCEPTION block.
>>
> Hehe.. good idea...
> 
> In the mean time I had thought about using advisory locks for the same
> thing, but the old-fashioned locks work fine too.

Or don't use the DBMS that way at all.

-- 
Lew


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