[GENERAL] Delay INSERT

2005-03-23 Thread ON.KG 
Hi

Does PostgreSQL have something like "INSERT DELAYD" - like it is used in
MySQL?

or any other way to delay inserting?

Thanx


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[GENERAL] duplicate key violates unique constraint

2005-06-13 Thread ON.KG 
Hi All!

I have table:

CREATE TABLE table1 (
   ip char(15) NOT NULL,
   hits integer NOT NULL default '1',
   PRIMARY KEY (ip)
);

So it's counting hits per each IP for current day and every day
trancated by cron:
TRUNCATE TABLE table1;

before inserting or updating this table there're some checkings,
logs, etc., so I'm using PL/PgSQL for that

after all checkings and logs I have:

  UPDATE table1
  SET hits = hits + 1
  WHERE ip = some_ip;

  IF NOT FOUND THEN
 INSERT INTO table1
(ip)
 VALUES
(some_ip);
  END IF;

when IP is not found in table it inserts new record into table
but in logs i see error
ERROR:  duplicate key violates unique constraint "table1"
CONTEXT:  PL/pgSQL function "insert_table1" line 68 at SQL statement

But record is inserted into table

what may be the problem?

i also tried before:
  SELECT INTO cnt hits
  FROM table1
  WHERE ip = some_ip;

  IF FOUND THEN
 UPDATE table1
 SET hits = hits + 1
 WHERE ip = some_ip;
  ELSE
 INSERT INTO table1
(ip)
 VALUES
(some_ip);
  END IF;

But same error still appears

Thank You


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[GENERAL] PostgreSQL on Guest Host (VMWare)

2004-11-15 Thread ON.KG 
Hi!

Description:
VMware 4.0
Main host is WinXP Pro (on FAT32)
and Guest Host is WinXP Pro (on NTFS)

On Guest Host - PostgreSQL 8.0-beta2-dev3

From Main host i'm trying to connect to PostgreSQL to Guest host
But as a result i'm receiving next message:

Connection Refused (0x274D/10061)
Is the server running on host xxx.xxx.xxx.xxx and accepting TCP/IP
connections on port 5432? 

Tell me please, what is the problem?

Thanx

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

[GENERAL] Trigger before insert

2004-11-25 Thread ON.KG 
Hi all,

===
CREATE FUNCTION trigger_test_func()
RETURNS trigger
AS '
 DECLARE
 cnt int4;
 
 BEGIN
   SELECT INTO cnt COUNT(*)
   FROM table_test
   WHERE ip = new.ip;

   IF cnt > 50 THEN
 -- THERE THE "INSERT" HAS TO BE STOPED
   END IF;

   RETURN new;
 END;'
LANGUAGE 'plpgsql';

CREATE TRIGGER trigger_test
BEFORE INSERT
ON table_test
FOR EACH ROW
EXECUTE PROCEDURE trigger_test_func();
===

How could i stop Inserting record into table by some condition?

Thanx!


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Trigger before insert

2004-11-25 Thread ON.KG 
Hi!

>> How could i stop Inserting record into table by some condition?

RH> RETURN null when using a before trigger. Or raise an exception to abort
RH> the whole transaction.

Thanx ;)
RETURN NULL works so as i need


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[GENERAL] table name in pl/pgsql

2004-11-25 Thread ON.KG 
New question:

i have tables like
table_20041124,
table_20041125,
etc...

i'm trying to make function (for example):
=
CREATE FUNCTION get_count(text, text)
RETURNS int2 AS '
  DECLARE
cnt int4;
  BEGIN
SELECT INTO cnt COUNT(*)
FROM table_$1   -- That doesn't work
WHERE key = $2;

RETURN cnt;
  END;'
LANGUAGE 'plpgsql';
=

call this function by:

=
SELECT get_count("20041124", "something");
=

string in funstion -  FROM table_$1

how could i get a final correct table name here?

Thanx!


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

   http://archives.postgresql.org

[GENERAL] Select Database

2004-12-02 Thread ON.KG 
Hi All!

How could I select another database without new connection?

For example, in PHP+MySQL we have mysql_select_db('database_name');

Thanx


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Select Database

2004-12-02 Thread ON.KG 
Thanx

JW> On 12/2/2004 4:39 AM, ON.KG wrote:

>> Hi All!
>> 
>> How could I select another database without new connection?
>> 
>> For example, in PHP+MySQL we have mysql_select_db('database_name');

JW> You can't. An existing session cannot change the database connected to.


JW> Jan


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

   http://archives.postgresql.org

[GENERAL] Check if table exists

2004-12-17 Thread ON.KG 
Hi ALL!

I need to check before selection records from table - does this table
exist
How can i do that?

Thanx in advance


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Check if table exists

2004-12-17 Thread ON.KG 
Richard Huxton & Riccardo G. Facchini
Thank you very much!


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

   http://archives.postgresql.org

Re: [GENERAL] A "cascade on delete" constraints deletes AFTER the source is gone??

2004-12-20 Thread ON.KG 
Hi All!

I need to make function which emulates table and returns setof columns
of this "table"

for example, i'm making query: "SELECT * FROM my_table(user_id)"

and function
CREATE OR REPLACE FUNCTION my_table (integer)
RETURNS setof text
AS '
  DECLARE
check_date date;
max_date date;
r record;

  BEGIN
max_date   := $2;
check_date := $3;

WHILE (check_date >= max_date) LOOP
  table_nam := ''table_''||to_char(check_date, ''MMDD'');
  
  FOR r IN EXECUTE ''SELECT COUNT(*) as cnt,
  AVG(amount) as avg_amount, SUM(amount) as sum_amount
FROM ''||table_nam||''
WHERE user_id = ''||$1||'' ''
  LOOP
RETURN NEXT r.cnt || r.avg_amount || r.sum_amount;
  END LOOP;

  check_date := check_date - interval ''1 day'';
END LOOP;

RETURN;
  END;'
LANGUAGE 'plpgsql';


As a result i need to get a records, and will be able to use each
column from it

But my function doesn't work
What is wrong?

Thanx


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[GENERAL] PL/Perl

2005-01-07 Thread ON.KG 
Hi!

Could I use "use", "require" functions in plperl?

for example,

CREATE OR REPLACE FUNCTION perl_func (text)
RETURNS real
AS '
  use HTTP::Request;
  use HTTP::Headers;
  
  return $value;
'
LANGUAGE 'plperl';


with me it doesn't work and returns error message
"Query failed: ERROR: creation of function failed: 'require' trapped by
operation mask at (eval 2) line 2. in ..."

Thanx


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[GENERAL] PL/Perl

2005-01-11 Thread ON.KG 
Hi All!

I'm trying in 'plperl' forking the processes by 'fork' function,
but receiving this message

Warning: pg_exec(): Query failed: ERROR: creation of function failed: 'fork' 
trapped by operation mask at (eval 2) line 11.

Does it mean, that in 'plperl' I can't use 'fork' function???

function example
=
CREATE OR REPLACE FUNCTION perl_fork_test ()
RETURNS int2
AS '
my %pid;
  
my @urls = (
  "http://domain1.com/index.php";,
  "http://domain2.com/index.php";
);
  
foreach my $url (@urls) {
  unless ($pid{$url} = fork) {
my $html = qx/GET "$url"/;

$ENV{TERM} = &my_exit;

sub my_exit {
  exit(0);
}
  }
}

sleep 6;
foreach my $url (keys %pid) {
  kill SIGTERM, $pid{$url};
}

wait;
  
return 1;
'
LANGUAGE 'plperl';
===
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
===

Thanx


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings