Use case statement and sum to get a count where status=20...
For example
Select sum(case when status=20 then 1 else 0 end) as status20,
Sum(case when status=30 then 1 else 0 end) as status30
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Br
Try this query
select a.relname as base,a.relkind
from
pg_class a
join pg_depend d on (a.oid = d.refobjid)
join pg_class c on (d.classid = c.oid)
join pg_rewrite r on (objid = r.oid)
join pg_class v on (ev_class = v.oid)
where a.relkind in('r', 'v')
and a.relname <> v.relname
and v.relname='YOUR
Try
select into a now() - interval ($1 || ' day')
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Richard Ray
Sent: Tuesday, December 19, 2006 3:10 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Help with quotes in plpgsql
How should this be properly qu
Use a trigger instead, the rule is only run once per insert/update/delete
while the trigger is run for each row.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of William Scott Jordan
Sent: Wednesday, December 13, 2006 9:05 PM
To: pgsql-sql@postgresql.org
S
Scott,
I use the following query with psql \o option. Change the schema name from
public to whatever. I am sure you could put
this into a plpgsql function using execute as well.
Jim
\o drops.sql
select 'drop function ' || nspname || '.' || proname || '(' ||
pg_catalog.oidvectortypes(p.proar
check out pg_stat_user_indexes, you will need to turn on the stats collection
in your postgresql.conf file first.
Jim
-- Original Message ---
From: "George Pavlov" <[EMAIL PROTECTED]>
To:
Sent: Tue, 1 Aug 2006 09:05:34 -0700
Subject: [SQL] finding unused indexes?
> Anybody hav
try
now() + (? || ' day')::interval
-- Original Message ---
From: "Marc G. Fournier" <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Wed, 26 Jul 2006 18:29:32 -0300 (ADT)
Subject: [SQL] DBD::Pg ... how would I format this prepare?
> I need to do:
>
> NOW() + '2 day'::in
use plperl
-- Original Message ---
From: T E Schmitz <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Fri, 07 Jul 2006 20:23:50 +0100
Subject: Re: [SQL] SELECT substring with regex
> Rodrigo De Leon wrote:
> > On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote:
> >
> >> But
James,
I know Postgresql doesn't have 2 arg aggregate functions. what you could do is
the following (untested)
select distict product_id,
sum(case when purchased then 1 else 0 end) as purchased,
sum(case when was_selected then 1 else 0 end) as was_selected
from some_table
group by product_id;
try www.slony.info
-- Original Message ---
From: "Mark Adan" <[EMAIL PROTECTED]>
To:
Sent: Wed, 14 Jun 2006 08:50:23 -0700
Subject: Re: [SQL] Good examples of calling slony stored procedures
> Hi
>
> Can somebody direct me to the mailing list for slony. I couldn't find
> it
You will have to use the "CALLED ON NULL INPUT" option to "create function"
(Postgresql 8.1, I don't know about other
versions) if you expect NULL arguments.
Jim
-- Original Message ---
From: "Rommel the iCeMAn" <[EMAIL PROTECTED]>
To: "PostgreSQL SQL Mailing List"
Sent: Fri,
or
psql db <
To:
Sent: Thu, 6 Apr 2006 14:37:51 -0700
Subject: Re: [SQL] Query from shell
> Judith wrote:
>
> >Hi every body, somebody can show me hot to execute a
> > query from a shell
>
> echo QUERY HERE | psql databasename
>
> Or, if you want to run several queries, run psql and
try
select ...,sum(case when sales.itemname = 'some' then sales.count else 0 end)
as "Sales Candies"
from your_table_here
group by ...
-- Original Message ---
From: "Daniel Hernandez" <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Thu, 23 Feb 2006 12:46:44 -0500 (EST)
Try this rule instead
create rule checks_d0 as
on delete to checks
do delete from checkitems
where ckid = OLD.ckid;
-- Original Message ---
From: "Milen A. Radev" <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Sun, 05 Feb 2006 15:10:23 +0200
Subjec
again, do you really want to join the tables or do a UNION ALL. From one of
your posts you said the table were the
same.
you need to do something like
select * from table_001
union all
select * from table_002
...
select * from table_999
I would do this in a set returning function looping of an
do you mean UNION ALL instead of JOIN, if you mean UNION ALL , I would go with
a set returning function passing it
the necessary WHERE clause to be applied to all of your tables. You might be
able to wrap the whole thing into a view
-- Original Message ---
From: solarsail <[
ti-column returns from pgsql
> On 7/22/05, Jim Buttafuoco <[EMAIL PROTECTED]> wrote:
> > Mark,
> >
> > Instead of RETURN NEXT rec.txt1; RETURN NEXT rec.txt2; just use RETURN
> > NEXT rec;
> >
> > then your select statement would be
> > sele
Mark,
Instead of RETURN NEXT rec.txt1; RETURN NEXT rec.txt2; just use RETURN NEXT
rec;
then your select statement would be
select * from my_func() as (txt1 text,txt2 text);
Jim
-- Original Message ---
From: "Mark R. Dingee" <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
use pg_dump with the --schema-only and --table= params
-- Original Message ---
From: "Mark Fenbers" <[EMAIL PROTECTED]>
To: Pg SQL Discussion Group
Sent: Mon, 18 Jul 2005 12:50:54 -0400
Subject: [SQL] Dumping table definitions
> I am looking for a way to reformat the informatio
I use the following function which returns a date series. You can modify it to
return an int series instead
create or replace function alldates(date,date) returns setof date
as
'
declare
s alias for $1;
e alias for $2;
d date;
begin
d := s;
while d <= e
did you restart postgresql and use the pg_stat_activity view instead (just to
save some typing).
Jim
-- Original Message ---
From: Erik Wasser <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Fri, 1 Jul 2005 15:58:46 +0200
Subject: [SQL] 'show full processlist' in postgre
try case
for example
select case when bool_column then 'Yes' else 'No end from your_table;
-- Original Message ---
From: Roy Souther <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Mon, 27 Jun 2005 11:16:58 -0600
Subject: [SQL] How can I simply substatue a value in a que
if you need a multi column fk don't use the "references" keyword on your create
table, instead use the "FOREIGN KEY"
keyword for the table, see the "create table" help.
so for example (untested) change
CREATE TABLE appalto (
cod_op int not null references Opere,
cod_co
works fine for me. Do you have any triggers on the tables or other rules? Can
you provide a complete SQL script that
starts from an empty database.
Jim
-- Original Message ---
From: Neil Dugan <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Thu, 16 Jun 2005 13:38:58 +1
Mike,
I posted this RULE also on hackers
CREATE or replace RULE crypto_view_delete_rule
AS ON DELETE
TO crypto_view
DO INSTEAD
(
select func_delFromCrypto( OLD.id,OLD.crypted_content);
);
Jim
-- Original Message ---
From: "Moran.Michael" <[EMAIL PROTECTED]>
To: pgsql-sql@pos
t now I know that if want a VARCHAR, I gotta manipulate it
> as a TEXT within my function when using PGCrypto. Any idea why?
>
> Thank again, Jim!
>
> _
>
> From: Jim Buttafuoco [mailto:[EMAIL PROTECTED]
> Sent: Thu 3/24/2005 9:14 AM
> To: Moran.Michael; pgsql-sql@post
give this a try
CREATE OR REPLACE FUNCTION selectFromCrypto(INTEGER)
RETURNS VARCHAR
AS '
DECLARE
_pid ALIAS FOR $1;
c text;
BEGIN
SELECT decrypt(crypted_content, decode(''password''::text,
''escape''::text), ''aes''::text) into c
FROM crypto
WHERE pid = _pid;
RET
Stef,
I use dblink to attach to both databases and query pg_namespace, pg_class,
pg_attribute ... to get the diffs. See
attached as an example. look for the dblink_connect lines to specify your
database. You will need to install
contrib/dblink. I used this with 7.4.X series and have NOT tes
have you reindexes your tables. When I was running 7.1.4, I ran a vacuum and reindex
nightly. Otherwise your index
files will keep getting bigger and bigger (this has been fixed in 7.4).
Jim
-- Original Message ---
From: patrick ~ <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Sen
29 matches
Mail list logo
