Re: [SQL] regexp_replace usage
Thanks !Michael Fuhr <[EMAIL PROTECTED]> wrote:Offhand I can't think of a way to do what you want with regexp_replace()but you could use PL/Perl. Something like this should work:CREATE FUNCTION mcfix(text) RETURNS text AS $$ $_[0] =~ s/\bMc([a-z])/Mc\u$1/g; return $_[0];$$ LANGUAGE plperl IMMUTABLE STRICT;SELECT mcfix('John Mcneil');mcfix- John McNeil(1 row)
How low will we go? Check out Yahoo! Messengers low PC-to-Phone call rates.
[SQL] Doubt with Joins
Hi list,I have the following query:select distinct cli.id as id, cli.nome as Nome, c.numero as cardpass, cli.documento as Documento, cli.endereco as Endereco,cli.complemento as Complemento, cli.bairro as Bairro, cli.cidade as Cidade, cli.estado as UF, cli.cep as CEP, cli.telefone as Telefone, cli.email, cli.sexo as Sexo, cli.estado_civil, cli.data_nascimento, cli.escolaridade, c.pontos, cli.informacoes_cliente from base.cliente as cli, base.cartao as c, base.sessao as s, base.evento as e, base.ingresso as iwhere c.cliente_id = cli.idand c.id = i.cartao_id <-- records only appears when I have records on table "i" and s.id = i.sessao_id <-- records only appears when I have records on table "i"and s.evento_id = e.id <-- records only appears when I have records on table "e" and cli.Nome Like 'ANA%'and e.nome = 'EVENT1' <-- Some times I don't want to pass this parameterand s.descricao = 'SESSION 1' <-- Some times I don't want to pass this parameter and c.estado = 1order by 1but my query vary according my parameters. My question is why I only get records that exist in both tables with this "ids" ? (event e and session s)It is a case of an outer join ? What should I do if I only have a cli.Nome parameter passed ?I don't know I I made myself clear.RegardsEzequias-- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-A pior das democracias ainda é melhor do que a melhor das ditadurasThe worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/
[SQL] SEQUENCES
Hi all, I need to get all sequences and their respective current values! Is there any catalog table or any other away to get this??? Thanks in advance.
Re: [SQL] SEQUENCES
On Mon, Oct 02, 2006 at 01:39:38PM -0300, Rodrigo Sakai wrote: > > I need to get all sequences and their respective current values! Is there > any catalog table or any other away to get this??? Here's a quick way to do it in a shell script, although it'd be sort of inefficient: for name in `psql -c "select relname from pg_class where relkind = 'S'" dbname; do psql -c "select last_value from $name" dbname; done. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] SEQUENCES
Rodrigo Sakai wrote on 02.10.2006 18:39: Hi all, I need to get all sequences and their respective current values! Is there any catalog table or any other away to get this??? Quote from the manual at: http://www.postgresql.org/docs/8.1/static/catalog-pg-class.html "The catalog pg_class catalogs tables and most everything else that has columns or is otherwise similar to a table. This includes indexes (but see also pg_index), sequences, views, composite types" Thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Time interval sums
How does one sum interval sums?
the following does not work:
select sum(stop-start::interval) as total from startstop
where sid= 545 and
2006 = date_part('year', day) and
9 = date_part('month', day)
however select stop-start::interval) as total from startstop
where sid= 545 and
2006 = date_part('year', day) and
9 = date_part('month', day)
works great but I need an overall sum
Table schema:
Table "public.startstop"
Column | Type | Modifiers
++---
sid| integer| not null
start | time without time zone |
stop | time without time zone |
day| date | not null
thanks
--
Arthur R. Van Hook
Mayor
The City of Lake Lotawana
[EMAIL PROTECTED]
(816) 578-4704 - Home
(816) 578-4215 - City
(816) 564-0769 - Cell
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Time interval sums
"A. R. Van Hook" <[EMAIL PROTECTED]> writes: > How does one sum interval sums? > the following does not work: > select sum(stop-start::interval) as total from startstop Define "does not work"? What did you get, what were you hoping for? Personally I'd drop the unnecessary cast, but AFAICS there are appropriate operators at least as far back as 7.3. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to FindNearest
On Sat, Sep 30, 2006 at 11:43:40 +0300, Alexander Ilyin <[EMAIL PROTECTED]> wrote: > > Suppose we have huge table. On the screen we need to show contents of that > table in the Grid and under that Grid user can input in TextBox some letters. > OnChange of this TextBox we need to make current row selection in the Grid on > the row with value of some column nearest to the user input in the TextBox. > > How this can be implemented in PostgreSQL? You can search for the row with the closest above and the row with the closest below and then return whichever these is closer. (Remember to handle the case where the target is larger or smaller than all values in the database.) You can use ORDER BY and LIMIT 1 and an appropiiate WHERE clause (something like columnname >= targetvalue) to do this. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Assigning a timestamp without timezone to a timestamp with timezone
Hi Does any one have any ideas for the following problem? Two tables both the have open and close columns that are timestamp or timestamp with time zone. One row in first table represents the corporate office default open and close times for all stores relative to the store?s own time zone for a particular day. The second table represents the specific open and close time for a specific store for a specific day, occasionally a store?s hours can be different from the corporate default. Table1: open_time timestamp close_time timestamp Table2: store_number int open_timetimestamp with timezone close_time timestamp with timezone I would like to be able to initialize table 2 from table 1. Suppose I had a store table that contained Store_table: Store_number int Store_tz char(03) I would like to do something like: Insert into Table2 Select S.store_number ,cast(T1.open_time as timestamp with timezone at S.Store_tz) ,cast(T1.close_time as timestamp with timezone at S.Store_tz) from Store_table S, Table1 T1 -- View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6613652 Sent from the PostgreSQL - sql mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] LOG: logger shutting down
My postmaster won't start. This is on a Fedora Core fc5 box, new and clean. It's postgres 8.1.4. All I get is "LOG: logger shutting down" in the pgstartup.log. I've checked for permission errors. At the relevant moment I get: # sh -x /etc/init.d/postgresql start ... ++ cat /var/lib/pgsql/data/PG_VERSION + '[' x8.1 '!=' x8.1 ']' + echo -n 'Starting postgresql service: ' Starting postgresql service: + echo -n 'City CarShare Hacking' + runuser -l postgres -c '/usr/bin/postmaster -p '\''5432'\'' -D '\''/var/lib/pgsql/data'\'' &' + sleep 2 LOG: logger shutting down ++ pidof -s /usr/bin/postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] LOG: logger shutting down
Bryce Nesbitt <[EMAIL PROTECTED]> writes: > My postmaster won't start. This is on a Fedora Core fc5 box, new and clean. > It's postgres 8.1.4. All I get is "LOG: logger shutting down" in the > pgstartup.log. My first instinct is to think selinux issue. Are you up2date on selinux policy patches? A restorecon on the postgres executables and everything under /var/lib/pgsql might help. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
