From rsmann123@yahoo.com Wed Jun  16 17:26:41 1999
Received: from web1006.mail.yahoo.com (web1006.mail.yahoo.com [128.11.23.96])
	by hub.org (8.9.3/8.9.3) with SMTP id NAA57094
	for <pgsql-sql@postgresql.org>; Wed, 16 Jun 1999 13:26:41 -0400 (EDT)
	(envelope-from rsmann123@yahoo.com)
Message-ID: <19990616172634.8215.rocketmail@web1006.mail.yahoo.com>
Received: from [198.146.87.154] by web1006.mail.yahoo.com;
	Wed, 16 Jun 1999 10:26:34 PDT
Date: Wed, 16 Jun 1999 10:26:34 -0700 (PDT)
From: Ravneet Mann <rsmann123@yahoo.com>
Subject: help needed
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Hi !

I am trying to test the Postgresql database on different networks. For
this purpose I want to find out how much time does it take to execute
each query. Can anyone tell me how to calculate the database retreival
time for a query ?

Any help will ne highly appriciated.

Regards,
Ravneet. 

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com




From Paul_Mowat@bmc.com Tue Aug  27 09:21:53 2002
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id DC00E475A05
	for <pgsql-sql@postgresql.org>; Tue, 27 Aug 2002 05:21:53 -0400 (EDT)
Received: from creeper.bmc.com (camaro.bmc.com [198.207.223.231])
	by postgresql.org (Postfix) with ESMTP id 9555F4758F8
	for <pgsql-sql@postgresql.org>; Tue, 27 Aug 2002 05:21:52 -0400 (EDT)
Received: from ec02-hou.bmc.com (localhost [127.0.0.1])
	by creeper.bmc.com (8.10.2/8.10.2) with ESMTP id g7R9LSF27513
	for <pgsql-sql@postgresql.org>; Tue, 27 Aug 2002 04:21:28 -0500 (CDT)
Received: by ec02-hou.bmc.com with Internet Mail Service (5.5.2653.19)
	id <RB0PV2BG>; Tue, 27 Aug 2002 04:21:00 -0500
Message-ID: <910A9F3B59D5D211A1970008C733B43405A79227@es01-cam.bmc.com>
From: "Mowat, Paul" <Paul_Mowat@bmc.com>
To: "'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
Subject: Help needed
Date: Tue, 27 Aug 2002 04:20:58 -0500
Importance: high
X-Priority: 1
Sensitivity: Company-Confidential
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2653.19)
Content-Type: multipart/mixed;
 boundary="----_=_NextPart_000_01C24DAB.0D7DA680"
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200208/387
X-Sequence-Number: 9233


This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_000_01C24DAB.0D7DA680
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C24DAB.0D7DA680"



------_=_NextPart_001_01C24DAB.0D7DA680
Content-Type: text/plain;
	charset="iso-8859-1"

Dear postgresql,
I am just starting to learn SQL.
I have attached a script that allows me to create some tables and indexes. I
have rows that are inserted into the tables.
 
I need some help with creating multiple subqueries and table joins, (six or
seven table joins).
Can you help?

Paul Mowat 
Software QA Engineer 
Enterprise Data Management 
--------------------------------------- 
BMCSoftware 
Abingdon UK 

Direct: +44 1235 827408 
Fax:    +44 1235 827430 
Email: paul_mowat@bmc.com. 
www.bmc.com 

 

------_=_NextPart_001_01C24DAB.0D7DA680
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; charset=3Diso-8859-=
1">


<META content=3D"MSHTML 6.00.2715.400" name=3DGENERATOR></HEAD>
<BODY style=3D"COLOR: #000000; FONT-FAMILY: Arial">
<DIV><SPAN class=3D339321709-27082002><FONT size=3D2>Dear=20
postgresql,</FONT></SPAN></DIV>
<DIV><SPAN class=3D339321709-27082002><FONT size=3D2>I am just starting to =
learn=20
SQL.</FONT></SPAN></DIV>
<DIV><SPAN class=3D339321709-27082002><FONT size=3D2>I have attached a scri=
pt that=20
allows me to create some tables and indexes. I have rows that are inserted =
into=20
the tables.</FONT></SPAN></DIV>
<DIV><SPAN class=3D339321709-27082002><FONT size=3D2></FONT></SPAN>&nbsp;</=
DIV>
<DIV><SPAN class=3D339321709-27082002></SPAN><FONT=20
size=3D2>I&nbsp;need&nbsp;some&nbsp;help&nbsp;with&nbsp;creating&nbsp;multi=
ple&nbsp;subqueries&nbsp;and&nbsp;table&nbsp;joins,&nbsp;(six&nbsp;or&nbsp;=
seven&nbsp;table&nbsp;joins).</FONT></DIV>
<DIV><SPAN class=3D339321709-27082002></SPAN><SPAN=20
class=3D339321709-27082002></SPAN><FONT size=3D2>C<SPAN class=3D339321709-2=
7082002>an=20
you help?</SPAN></FONT></DIV>
<P><FONT face=3D"Times New Roman" size=3D2>Paul Mowat</FONT> <BR><FONT=20
face=3D"Times New Roman" size=3D2>Software QA Engineer </FONT><BR><FONT=20
face=3D"Times New Roman" size=3D2>Enterprise Data Management</FONT> <BR><FO=
NT=20
face=3D"Times New Roman" size=3D2>---------------------------------------</=
FONT>=20
<BR><FONT face=3DArial color=3D#0000ff size=3D4>BMC</FONT><FONT face=3DAria=
l=20
color=3D#c0c0c0 size=3D4>Software</FONT> <BR><FONT face=3D"Times New Roman"=
=20
size=3D2>Abingdon UK</FONT> </P>
<P><FONT face=3D"Times New Roman" size=3D2>Direct: +44 1235 827408</FONT> <=
BR><FONT=20
face=3D"Times New Roman" size=3D2>Fax:&nbsp;&nbsp;&nbsp; +44 1235 827430</F=
ONT>=20
<BR><FONT face=3D"Times New Roman" size=3D2>Email: paul_mowat@bmc.com.</FON=
T>=20
<BR><FONT face=3D"Times New Roman" size=3D2>www.bmc.com</FONT> </P>
<DIV>&nbsp;</DIV></BODY></HTML>

------_=_NextPart_001_01C24DAB.0D7DA680--

------_=_NextPart_000_01C24DAB.0D7DA680
Content-Type: application/octet-stream;
	name="BaseSetofObjects.sql"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
	filename="BaseSetofObjects.sql"

-- (c) Copyright 2002 BMC Software Ltd. All rights reserved.
-- This QA scripts creates a base set of objects so the Query Rewrite state=
ments (40) can be tested.
-- Four tables are created.
-- Creates six indexes.
-- To include columns with non-distinct keys.
-- Columns with NULL.
-- Join with non-distinct matches on both sides of the join.
-- INserts data into the table and indexes.
-- This scripts will be run on all Oracle releases.
-- Created by Paul Mowat

CREATE TABLE QAT0001IUN
(
  c01 NUMBER CONSTRAINT QAT0001iunc01 NOT NULL,
  -- unique single column index
  c02 VARCHAR2(30) CONSTRAINT QAT0001iunc02 NOT NULL,=20
  -- unindexed description
  c03 NUMBER CONSTRAINT QAT0001iunc03 NOT NULL,=20=20=20=20=20=20=20=20=20=
=20=20=20=20
  -- non-unique single column index  MOD (key,10)
  c04 NUMBER CONSTRAINT QAT0001iunc04 NOT NULL,=20=20=20=20=20=20=20=20=20=
=20=20=20=20
  -- non-unique single column index  MOD (key,100)
  c05 NUMBER CONSTRAINT QAT0001iunc05 NOT NULL,=20=20=20=20=20=20=20=20=20=
=20=20=20=20
  -- non-unique single column index  MOD (key,1000)
  c06 NUMBER CONSTRAINT QAT0001iunc06 NOT NULL,=20=20=20=20=20=20=20=20=20=
=20=20=20=20
  -- non-unique single column index  TRUNC (random * 100)
  c07 NUMBER=20
  -- non-unique single column index  TRUNC (key / 2)=20=20=20=20
  -- for use in CONNECT BY
  -- no NOT NULL constraint
)
TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

--Table 2 created without the NULL constraints of table 1.

CREATE TABLE QAT0002IUN
(
   c01 NUMBER,
  -- unique single column index
  c02 VARCHAR2(30),=20
  -- unindexed description
  c03 NUMBER,=20=20=20=20=20=20=20=20=20=20=20=20=20
  -- non-unique single column index  MOD (key,10)
  c04 NUMBER,=20=20=20=20=20=20=20=20=20=20=20=20=20
  -- non-unique single column index  MOD (key,100)
  c05 NUMBER,=20=20=20=20=20=20=20=20=20=20=20=20=20
  -- non-unique single column index  MOD (key,1000)
  c06 NUMBER,=20=20=20=20=20=20=20=20=20=20=20=20=20
  -- non-unique single column index  TRUNC (random * 100)
  c07 NUMBER=20
  -- non-unique single column index  TRUNC (key / 2)=20=20=20=20
  -- for use in CONNECT BY
  -- no NOT NULL constraint
)
TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

--Table 3 created with no indexes.

CREATE TABLE QAT0003IUN
(
  c01 NUMBER CONSTRAINT QAT0003iunc01 NOT NULL,
  -- unique single column index
  c02 VARCHAR2(30) CONSTRAINT QAT0003iunc02 NOT NULL,=20
  -- unindexed description
  c03 NUMBER CONSTRAINT QAT0003iunc03 NOT NULL,=20=20=20=20=20=20=20=20=20=
=20=20=20=20
  -- non-unique single column index  MOD (key,10)
  c04 NUMBER CONSTRAINT QAT0003iunc04 NOT NULL,=20=20=20=20=20=20=20=20=20=
=20=20=20=20
  -- non-unique single column index  MOD (key,100)
  c05 NUMBER CONSTRAINT QAT0003iunc05 NOT NULL,=20=20=20=20=20=20=20=20=20=
=20=20=20=20
  -- non-unique single column index  MOD (key,1000)
  c06 NUMBER CONSTRAINT QAT0003iunc06 NOT NULL,=20=20=20=20=20=20=20=20=20=
=20=20=20=20
  -- non-unique single column index  TRUNC (random * 100)
  c07 NUMBER=20
  -- non-unique single column index  TRUNC (key / 2)=20=20=20=20
  -- for use in CONNECT BY
  -- no NOT NULL constraint
)
TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

--Table 4 created to test all other types of data types.

CREATE TABLE QAT0004IUN
(
  c01 CHAR(30),
  c02 VARCHAR(30),
  C03 NUMBER(38),
  c04 VARCHAR2(30),
  C05 VARCHAR2(30),
  C06 VARCHAR(30),
  C07 CHAR (10) CONSTRAINT QAT004IUN NULL
)
  TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);=20=20=20=20=20=20=20=20=20=
=20=20=20=20


--Inserts into QAT0001IUN, Table 1.

DECLARE
  c_max_rows           CONSTANT NUMBER :=3D 1000;
  v_key                NUMBER;
  v_c01                QAT0001IUN.c01%TYPE;
  v_c02                QAT0001IUN.c02%TYPE;
  v_c03                QAT0001IUN.c03%TYPE;
  v_c04                QAT0001IUN.c04%TYPE;
  v_c05                QAT0001IUN.c05%TYPE;
  v_c06                QAT0001IUN.c06%TYPE;
  v_c07                QAT0001IUN.c07%TYPE;=20

BEGIN
  sim_lcg.initialise (1);

  FOR v_key IN 0..c_max_rows - 1 LOOP
    v_c01 :=3D v_key;
    v_c02 :=3D LPAD (TO_CHAR (v_key),20,'0');
    v_c03 :=3D MOD (v_key,20);
    v_c04 :=3D MOD (v_key,50);
    v_c05 :=3D MOD (v_key,1000);
    v_c06 :=3D TRUNC (sim_lcg.random * 100);
    IF v_key =3D 0 THEN
      v_c07 :=3D NULL;
    ELSE
      v_c07 :=3D TRUNC (v_key / 2);=20=20=20
    END IF;

    INSERT INTO QAT0001iun=20
    (
      c01,
      c02,
      c03,
      c04,
      c05,
      c06,
      c07
    )
    VALUES=20
    (
      v_c01,
      v_c02,
      v_c03,
      v_c04,
      v_c05,
      v_c06,
      v_c07
    );
    IF MOD (v_key,20000) =3D 19999 THEN
      COMMIT;
    END IF;
  END LOOP;=20=20

  COMMIT;
END;
/

--Inserts into QAT0002IUN,Table 2.

DECLARE
  c_max_rows           CONSTANT NUMBER :=3D 1000;
  v_key                NUMBER;
  v_c01                QAT0002IUN.c01%TYPE;
  v_c02                QAT0002IUN.c02%TYPE;
  v_c03                QAT0002IUN.c03%TYPE;
  v_c04                QAT0002IUN.c04%TYPE;
  v_c05                QAT0002IUN.c05%TYPE;
  v_c06                QAT0002IUN.c06%TYPE;
  v_c07                QAT0002IUN.c07%TYPE;=20

BEGIN
  sim_lcg.initialise (1);

  FOR v_key IN 0..c_max_rows - 1 LOOP
    v_c01 :=3D v_key;
    v_c02 :=3D LPAD (TO_CHAR (v_key),20,'0');
    v_c03 :=3D MOD (v_key,20);
    v_c04 :=3D MOD (v_key,100);
    v_c05 :=3D MOD (v_key,1000);
    v_c06 :=3D TRUNC (sim_lcg.random * 100);
    IF v_key =3D 0 THEN
      v_c07 :=3D NULL;
    ELSE
      v_c07 :=3D TRUNC (v_key / 2);=20=20=20
    END IF;

    INSERT INTO QAT0002iun=20
    (
      c01,
      c02,
      c03,
      c04,
      c05,
      c06,
      c07
    )
    VALUES=20
    (
      v_c01,
      v_c02,
      v_c03,
      v_c04,
      v_c05,
      v_c06,
      v_c07
    );
    IF MOD (v_key,20000) =3D 19999 THEN
      COMMIT;
    END IF;
  END LOOP;=20=20

  COMMIT;
END;
/


--Inserts into QAT0003IUN,Table 3.

DECLARE
  c_max_rows           CONSTANT NUMBER :=3D 1000;
  v_key                NUMBER;
  v_c01                QAT0003IUN.c01%TYPE;
  v_c02                QAT0003IUN.c02%TYPE;
  v_c03                QAT0003IUN.c03%TYPE;
  v_c04                QAT0003IUN.c04%TYPE;
  v_c05                QAT0003IUN.c05%TYPE;
  v_c06                QAT0003IUN.c06%TYPE;
  v_c07                QAT0003IUN.c07%TYPE;=20

BEGIN
  sim_lcg.initialise (1);

  FOR v_key IN 0..c_max_rows - 1 LOOP
    v_c01 :=3D v_key;
    v_c02 :=3D LPAD (TO_CHAR (v_key),20,'0');
    v_c03 :=3D MOD (v_key,20);
    v_c04 :=3D MOD (v_key,100);
    v_c05 :=3D MOD (v_key,1000);
    v_c06 :=3D TRUNC (sim_lcg.random * 100);
    IF v_key =3D 0 THEN
      v_c07 :=3D NULL;
    ELSE
      v_c07 :=3D TRUNC (v_key / 2);=20=20=20
    END IF;

    INSERT INTO QAT0003IUN=20
    (
      c01,
      c02,
      c03,
      c04,
      c05,
      c06,
      c07
    )
    VALUES=20
    (
      v_c01,
      v_c02,
      v_c03,
      v_c04,
      v_c05,
      v_c06,
      v_c07
    );
    IF MOD (v_key,20000) =3D 19999 THEN
      COMMIT;
    END IF;
  END LOOP;=20=20

  COMMIT;
END;
/

--Inserts into QAT0004IUN,Table 4.

DECLARE=20
  c_max_rows           CONSTANT NUMBER :=3D 1000;
  v_key                NUMBER;
  v_c01                QAT0004IUN.c01%TYPE;
  v_c02                QAT0004IUN.c02%TYPE;
  v_c03                QAT0004IUN.c03%TYPE;
  v_c04                QAT0004IUN.c04%TYPE;
  v_c05                QAT0004IUN.c05%TYPE;
  v_c06                QAT0004IUN.c06%TYPE;
  v_c07                QAT0004IUN.c07%TYPE;=20

BEGIN
  sim_lcg.initialise (1);

  FOR v_key IN 0..c_max_rows - 1 LOOP
    v_c01 :=3D v_key;
    v_c02 :=3D LPAD (TO_CHAR (v_key),20,'0');
    v_c03 :=3D MOD (v_key,20);
    v_c04 :=3D MOD (v_key,100);
    v_c05 :=3D MOD (v_key,1000);
    v_c06 :=3D TRUNC (sim_lcg.random * 100);
    IF v_key =3D 0 THEN
      v_c07 :=3D NULL;
    ELSE
      v_c07 :=3D TRUNC (v_key / 2);=20=20=20
    END IF;

    INSERT INTO QAT0004IUN=20
    (
      c01,
      c02,
      c03,
      c04,
      c05,
      c06,
      c07
    )
    VALUES=20
    (
      v_c01,
      v_c02,
      v_c03,
      v_c04,
      v_c05,
      v_c06,
      v_c07
    );
    IF MOD (v_key,20000) =3D 19999 THEN
      COMMIT;
    END IF;
  END LOOP;=20=20

  COMMIT;
END;
/


--CREATES A TABLE TO TEST THE Upper Function
CREATE TABLE TEST_UPPER
( C01 NUMBER, C02 VARCHAR2(30));

---INSERTS DATA INTO THE TEST_UPPER TABLE
INSERT INTO TEST_UPPER VALUES (1, 'ONE');
INSERT INTO TEST_UPPER VALUES (2, 'TWO');
INSERT INTO TEST_UPPER VALUES (3, 'THREE');
INSERT INTO TEST_UPPER VALUES (4, 'FOUR');
INSERT INTO TEST_UPPER VALUES (5, 'FIVE');
COMMIT;

--CREATES A TABLE TO TEST THE LOWER FUNCTION
CREATE TABLE TEST_LOWER
( C01 NUMBER, C02 VARCHAR2(30));

--INSERTS DATA INTO THE TEST_LOWER TABLE
INSERT INTO TEST_LOWER VALUES (1, 'ONE');
INSERT INTO TEST_LOWER VALUES (2, 'TWO');
INSERT INTO TEST_LOWER VALUES (3, 'THREE');
INSERT INTO TEST_LOWER VALUES (4, 'FOUR');
INSERT INTO TEST_LOWER VALUES (5, 'FIVE');
COMMIT;

--Creates six indexes for QAT0001iun.

CREATE INDEX QAT0001iuni01 ON QAT0001iun (c01)
TABLESPACE TS02
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0001iuni02 ON QAT0001iun (c03)
TABLESPACE TS02
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0001iuni03 ON QAT0001iun (c04)
TABLESPACE TS02
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0001iuni04 ON QAT0001iun (c05)
TABLESPACE TS02
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0001iuni05 ON QAT0001iun (c06)
TABLESPACE TS02
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0001iuni06 ON QAT0001iun (c07)
TABLESPACE TS02
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

--Creates four indexes for QAT0002iun.

CREATE INDEX QAT0002iuni01 ON QAT0002iun (c01, C02)
TABLESPACE TS02
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0002iuni02 ON QAT0002iun (c03)
TABLESPACE TS02
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0002iuni03 ON QAT0002iun (c04)
TABLESPACE TS02
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0002iuni04 ON QAT0002iun (c05)
TABLESPACE TS02
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);


--Creates four indexes for QAT0004IUN.

CREATE INDEX QAT0004IUNI01 ON QAT0004IUN (c01)
TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0004IUNI02 ON QAT0004IUN (c02)
TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0004IUNI03 ON QAT0004IUN (c03)
TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0004IUNI04 ON QAT0004IUN (c04)
TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0004IUNI05 ON QAT0004IUN (c05)
TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0004IUNI06 ON QAT0004IUN (c06)
TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0004IUNI07 ON QAT0004IUN (c07)
TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);


--Analyzes the stats for each table.

ANALYZE TABLE QAT0001iun COMPUTE STATISTICS;
ANALYZE TABLE QAT0002iun COMPUTE STATISTICS;
ANALYZE TABLE QAT0003iun COMPUTE STATISTICS;
ANALYZE TABLE QAT0004IUN COMPUTE STATISTICS;









------_=_NextPart_000_01C24DAB.0D7DA680--



From nosyman@gmail.com Wed Nov  30 09:18:43 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 20AD09DCD37
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 30 Nov 2005 05:18:43 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 01670-09-5
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 30 Nov 2005 05:18:41 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.203])
	by postgresql.org (Postfix) with ESMTP id 08A9D9DCD4E
	for <pgsql-sql@postgresql.org>; Wed, 30 Nov 2005 05:18:31 -0400 (AST)
Received: by wproxy.gmail.com with SMTP id i34so1956106wra
	for <pgsql-sql@postgresql.org>; Wed, 30 Nov 2005 01:18:33 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:from:to:subject:date:user-agent:references:in-reply-to:organization:mime-version:content-type:content-transfer-encoding:content-disposition:message-id;
	b=NO2xfJujIdqWnuP0bZHF9gALmXgR+51m02PDbXoFfjCxgunXYESg+ROdnFNh/afpA2ebN0RD3FtksnOb//LUMnQi7ZWHs5O/Ly7dreCQ7VJhugP8XtRYz+uf21fD/93dmQ21M436cFZ+LzgMrZWVN+vuODeo8Z+3BCp5o5ZTCAA=
Received: by 10.64.213.11 with SMTP id l11mr834978qbg;
	Wed, 30 Nov 2005 01:18:33 -0800 (PST)
Received: from aditatu.atlantic.ro ( [82.76.194.145])
	by mx.gmail.com with ESMTP id e10sm982505qbe.2005.11.30.01.18.31;
	Wed, 30 Nov 2005 01:18:32 -0800 (PST)
From: NosyMan <nosyman@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Help needed
Date: Wed, 30 Nov 2005 11:18:28 +0200
User-Agent: KMail/1.8.92
References: <200511291531.30441.leif@solumslekt.org>
	<200511291701.26148.leif@solumslekt.org>
	<200511291747.49570.leif@solumslekt.org>
In-Reply-To: <200511291747.49570.leif@solumslekt.org>
Organization: Noscom
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200511301118.28795.nosyman@gmail.com>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200511/280
X-Sequence-Number: 23531

Hi everyone,

I have some kind of strange problem. I want to build general parametrized 
trigger for my database. The parameter sent to the trigger contains a column 
name that trigger should be check.  

CREATE OR REPLACE FUNCTION F_T_IU__check() RETURNS trigger AS $$
	DECLARE
		vt VARCHAR;
	BEGIN
...........................................................
			vt:='constant_value_id';
			RAISE EXCEPTION 'aaa=%', NEW.vt;
.............................................................

The 'vt' variable contains the column name. In the above statement I want that 
NEW.vt to be interpreted as NEW.constant_value_id. It is possible to do this?

Thanks,
Nosy



From dev@archonet.com Wed Nov  30 10:35:56 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id A15B99DCB66
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 30 Nov 2005 06:35:56 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 06723-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 30 Nov 2005 06:35:56 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75])
	by postgresql.org (Postfix) with ESMTP id 7A8419DCB69
	for <pgsql-sql@postgresql.org>; Wed, 30 Nov 2005 06:35:53 -0400 (AST)
Received: from mainbox.archonet.com
	(84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99])
	by smtp.metronet.co.uk (MetroNet Mail) with ESMTP
	id 131F2401912; Wed, 30 Nov 2005 10:35:50 +0000 (GMT)
Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP id E5CF6FF1A;
	Wed, 30 Nov 2005 10:35:55 +0000 (GMT)
Message-ID: <438D808B.3030706@archonet.com>
Date: Wed, 30 Nov 2005 10:35:55 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5 (X11/20051025)
MIME-Version: 1.0
To: NosyMan <nosyman@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help needed
References: <200511291531.30441.leif@solumslekt.org>
	<200511291701.26148.leif@solumslekt.org>
	<200511291747.49570.leif@solumslekt.org>
	<200511301118.28795.nosyman@gmail.com>
In-Reply-To: <200511301118.28795.nosyman@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200511/281
X-Sequence-Number: 23532

NosyMan wrote:
> Hi everyone,
> 
> I have some kind of strange problem. I want to build general parametrized 
> trigger for my database. The parameter sent to the trigger contains a column 
> name that trigger should be check.  

> The 'vt' variable contains the column name. In the above statement I want that 
> NEW.vt to be interpreted as NEW.constant_value_id. It is possible to do this?

Not in pl/pgsql - try one of the more interpreted languages - 
tcl/perl/python etc.

Oh, and try not to reply to a previous message when starting a new 
thread - it can mess with threading in some mail packages.
-- 
   Richard Huxton
   Archonet Ltd