[GENERAL] auto-filling a field on insert

2009-10-21 Thread semi-ambivalent
If I have a table with fields A, B, C and D how could I do something
such that if I insert values in fields A, B and C field D would be
auto-filled with the value A||B||C? Just a pointer of where to begin
looking is fine. Triggers? Copy ... From? Table or field definitions?

thx

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


Re: [GENERAL] auto-filling a field on insert

2009-10-22 Thread semi-ambivalent
On Oct 21, 8:54 pm, da...@fetter.org (David Fetter) wrote:
> On Wed, Oct 21, 2009 at 07:17:44PM -0700, semi-ambivalent wrote:
> > If I have a table with fields A, B, C and D how could I do something
> > such that if I insert values in fields A, B and C field D would be
> > auto-filled with the value A||B||C? Just a pointer of where to begin
> > looking is fine. Triggers? Copy ... From? Table or field
> > definitions?
>
> Instead of populating your table with this extra information, create a
> VIEW with what you want in it.
>
> Cheers,
> David.
> --
> David Fetter http://fetter.org/
> Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
> Skype: davidfetter      XMPP: david.fet...@gmail.com
>
> Remember to vote!
> Consider donating to Postgres:http://www.postgresql.org/about/donate
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your 
> subscription:http://www.postgresql.org/mailpref/pgsql-general

At first blush that looks good but I need an index on that concatted
value and I don't seem to be able to index a field in a view. I'll
check the docs on views to see if I'm missing something.

thanks!

sa

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


Re: [GENERAL] auto-filling a field on insert

2009-10-23 Thread semi-ambivalent
On Oct 23, 5:10 am, cr...@postnewspapers.com.au (Craig Ringer) wrote:
> semi-ambivalent wrote:
> > At first blush that looks good but I need an index on that concatted
> > value and I don't seem to be able to index a field in a view. I'll
> > check the docs on views to see if I'm missing something.
>
> As others have noted, a multi-field index or a functional index is
> probably the best option.
>
> If you decide for some reason that you really, really need the
> concatenated fields stored in the table its self, you will need to use a
> BEFORE INSERT ... FOR EACH ROW trigger to populate field `D'.
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your 
> subscription:http://www.postgresql.org/mailpref/pgsql-general

Thanks everyone for the pointers. I like the idea of getting rid of
the concatenated field even though it reduced the worst case query
time to one tenth of what I had been seeing. But for now I'm going to
keep it there because I'm ignorant about triggers so this will be a
good opportunity to learn about them before I drop the column for
something more efficient, assuming there is.

sa

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


[GENERAL] How to find avg() of sum()?

2010-04-16 Thread semi-ambivalent
I have some data fields that I have summed, grouped by a date field.
The sums are different. How can I then calculate the average value for
those sums? Everything I've tried errors out with something along the
lines of using agregates where I can't, or for using multiple values
where that is not allowed. I'm sure this can be done in one query,
without temp tables, but I don't know it and haven't found it yet in
the docs.

thanks,

sa

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


[GENERAL] Changes in 8.2's PHP behaviour?

2006-12-18 Thread semi-ambivalent
All,

I have a simple web page that inserts data into a table in 8.1.5 using
PHP4. It's pretty amateurish but gets the job done. A few days ago I
upgraded to 8.2 (this is on a FreeBSD system, and I used the port) but
when I tried to use the web page this morning I got errors about:

PHP Fatal error:  Call to undefined function:  pg_escape_string() in
/usr/local/www/data-dist/some_dir/some_dir/dataInsert.php on line 9,
referer: http://localhost/some_dir/some_dir/dataEnter.php

I'm a pretty poor PHP person, and I think its error messages can be
cryptic, but nothing has changed in this except the version of
postgresql. Does 8.2 handle input differently than does 8.1.x? Is that
where to begin looking? I have seen talk about handling of escape
strings but thought is was 'this way is good and this way is bad' kind
of thing, not a 'this way will no longer work with postgresql' thing.
FWIW a quick pg_dump, a fallback to 8.1.5 and a restore and things work
again.

thanks for any pointers,

r


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/


Re: [GENERAL] Changes in 8.2's PHP behaviour?

2006-12-19 Thread semi-ambivalent

Erik Jones wrote:
> You probably need to re-compile PHP so that it can correctly link to the
> new postgres binaries.
>
> semi-ambivalent wrote:
> > All,
> >
> > I have a simple web page that inserts data into a table in 8.1.5 using
> > PHP4. It's pretty amateurish but gets the job done. A few days ago I
> > upgraded to 8.2 (this is on a FreeBSD system, and I used the port) but
> > when I tried to use the web page this morning I got errors about:
> >
> > PHP Fatal error:  Call to undefined function:  pg_escape_string() in
> > /usr/local/www/data-dist/some_dir/some_dir/dataInsert.php on line 9,
> > referer: http://localhost/some_dir/some_dir/dataEnter.php
> >
> > I'm a pretty poor PHP person, and I think its error messages can be
> > cryptic, but nothing has changed in this except the version of
> > postgresql. Does 8.2 handle input differently than does 8.1.x? Is that
> > where to begin looking? I have seen talk about handling of escape
> > strings but thought is was 'this way is good and this way is bad' kind
> > of thing, not a 'this way will no longer work with postgresql' thing.
> > FWIW a quick pg_dump, a fallback to 8.1.5 and a restore and things work
> > again.
> >
> > thanks for any pointers,
> >
> > r
> >
> >
> > ---(end of broadcast)---
> > TIP 4: Have you searched our list archives?
> >
> >http://archives.postgresql.org/
> >
>
>
> --
> erik jones <[EMAIL PROTECTED]>
> software development
> emma(r)
>
>
> ---(end of broadcast)---
> TIP 1: 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


!   :)

thanks (both)

s-a


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/


[GENERAL] Proper escaping for char(3) string, or PHP at fault, or me at fault?

2007-02-13 Thread semi-ambivalent
All,

I have a char(3) column that has occasional values of this:
(V)

In a PHP-called nested query I've a line something like:
select * from tableA where  = any (select date from tableA where void !
= '(V)') group by date order by record

This is throwing a query error in the log that blames the parens but
my efforts to properly escape them with backslashes or "E" doesn't
help. So perhaps this is an error in my PHP, and I'll deal with that,
but in the SQL portion shouldn't '\(V\)' do the trick? Or is it now
'E(VE)'?

thanks,

r


---(end of broadcast)---
TIP 1: 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