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
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
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 || '(' ||
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: pgsql-sql@postgresql.org
Sent: Tue, 1 Aug 2006 09:05:34 -0700
Subject: [SQL] finding unused
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'::interval
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
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 that takes
try www.slony.info
-- Original Message ---
From: Mark Adan [EMAIL PROTECTED]
To: pgsql-sql@postgresql.org
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
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
or
psql db EOQ
select...;
delete...;
update...;
\q
EOQ
-- Original Message ---
From: Owen Jacobson [EMAIL PROTECTED]
To: pgsql-sql@postgresql.org
Sent: Thu, 6 Apr 2006 14:37:51 -0700
Subject: Re: [SQL] Query from shell
Judith wrote:
Hi every body, somebody can show
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
Subject:
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
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
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
select * from my_func() as (txt1 text,txt2 text);
Jim
Besides a simple RETURN NEXT, you'll need
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 pgsql-sql@postgresql.org
Sent: Mon, 18 Jul 2005 12:50:54 -0400
Subject: [SQL] Dumping table definitions
I am looking for a way to
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
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
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_com
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
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;
?
Thank again, Jim!
_
From: Jim Buttafuoco [mailto:[EMAIL PROTECTED]
Sent: Thu 3/24/2005 9:14 AM
To: Moran.Michael; pgsql-sql@postgresql.org
Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??
give this a try
CREATE OR REPLACE FUNCTION selectFromCrypto(INTEGER)
RETURNS
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:
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
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]
28 matches
Mail list logo
