[SQL] trigger failed to identify the partions

[Sridhar Reddy Ratna]

 

Hi all,

 

I have created a table and partitions as below.

 

CREATE TABLE coll_fp_submission_details

(

  rrid numeric NOT NULL,

  sid numeric NOT NULL,

  pfid numeric NOT NULL,

  "timestamp" date NOT NULL,

  schema_version numeric NOT NULL,

  details character varying NOT NULL,

  app_txn_id character varying NOT NULL,

  CONSTRAINT coll_fp_submission_details_pkey PRIMARY KEY (rrid)

)

WITH (OIDS=FALSE);

 

CREATE TABLE coll_fp_subdtls_01

(

  CONSTRAINT coll_fp_subdtls_01_pkey PRIMARY KEY (rrid)

)

INHERITS (coll_fp_submission_details)

WITH (OIDS=FALSE)

TABLESPACE fpsdts01;

 

CREATE TABLE coll_fp_subdtls_02

(

  CONSTRAINT coll_fp_subdtls_02_pkey PRIMARY KEY (rrid)

)

INHERITS (coll_fp_submission_details)

WITH (OIDS=FALSE)

TABLESPACE fpsdts02;

 

 

 

Now created a trigger as below

 

CREATE OR REPLACE FUNCTION ins_submission_details()

RETURNS TRIGGER AS $$

  DECLARE

  dateTable TEXT;

  cmd TEXT;

BEGIN

 

IF ((NEW.rrid % 2)= 0) THEN

dateTable := coll_fp_subdtls_01;

ELSE

dateTable := coll_fp_subdtls_02;

 

END IF;

 

cmd := 'INSERT INTO ' || dateTable  ||
'(rrid,sid,pfid,timestamp,schema_version,details,app_txn_id)' ||

' VALUES (' ||  quote_ident(NEW.rrid) || ',' ||

quote_ident(NEW.sid) || ',' ||

quote_ident(NEW.pfid) || ',' ||

quote_literal(NEW.timestamp) || ',' ||

quote_ident(NEW.schema_version) || ',' ||

quote_literal(NEW.details) || ',' ||

quote_literal(NEW.app_txn_id) || ',';

 

EXECUTE cmd;

RETURN NULL;

END;

  $$LANGUAGE 'plpgsql';

 

 

 

CREATE TRIGGER trig_ins_submission_details

  BEFORE INSERT

  ON coll_fp_submission_details

  FOR EACH ROW

  EXECUTE PROCEDURE ins_submission_details();

 

 

 

Now I am trying to insert data into table 

 

INSERT INTO coll_fp_submission_details( rrid, sid, pfid, "timestamp",
schema_version, details, app_txn_id)VALUES (102, 101, 101, '2009-09-09',
1,'dtls', '1234');

 

The error is 

 

 

ERROR:  column "coll_fp_subdtls_01" does not exist

LINE 1: SELECT  coll_fp_subdtls_01

^

QUERY:  SELECT  coll_fp_subdtls_01

CONTEXT:  PL/pgSQL function "ins_submission_details" line 7 at assignment

 

 

** Error **

 

ERROR: column "coll_fp_subdtls_01" does not exist

SQL state: 42703

Context: PL/pgSQL function "ins_submission_details" line 7 at assignment

 

 

Can any body help me what is this problem and what is the solution.

 

Thanks in advance,

Sridhar Ratna


__

DISCLAIMER

The information contained in this e-mail message and/or attachments to it may
contain confidential or privileged information. If you are not the intended
recipient, any dissemination, use, review, distribution, printing or copying
of the information contained in this e-mail message and/or attachments to it
are strictly prohibited. If you have received this communication in error,
please notify us by reply e-mail or directly to netsupp...@cmcltd.com or
telephone and immediately and permanently delete the message and any
attachments. Thank you.


__

This email has been scrubbed for your protection by SecureMX.
For more information visit http://securemx.in
__

Re: [SQL] trigger failed to identify the partions

[Richard Huxton]

Sridhar Reddy Ratna wrote:
> 
> dateTable := coll_fp_subdtls_01;
> 
> ELSE
> 
> dateTable := coll_fp_subdtls_02;


> ERROR:  column "coll_fp_subdtls_01" does not exist
> 
> ERROR: column "coll_fp_subdtls_01" does not exist


I think you missed the word "column" in the error message (easy to do,
you know you are naming tables). You've missed the quotes around the
partition-names so it's trying to find a column on a table that matches.

  dateTable := 'coll_fp_subdtls_01';


-- 
  Richard Huxton
  Archonet Ltd

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] trigger failed to identify the partions

[Sridhar Reddy Ratna]

 

Hi Richard,

 

Thanks for your suggestion. It worked great.

 

But when I used table spaces for the inherited tables, data is being
inserted to the fpsdts01 or fpsdts02 along with the default table space.

So I am getting duplicate rows in select SQL.

 

I have created the table with default table space as below

 

CREATE TABLE coll_fp_submission_details(

  rrid numeric NOT NULL,

  sid numeric NOT NULL,

  pfid numeric NOT NULL,

  "timestamp" date NOT NULL,

  schema_version numeric NOT NULL,

  details character varying NOT NULL,

  app_txn_id character varying NOT NULL,

  CONSTRAINT coll_fp_submission_details_pkey PRIMARY KEY (rrid)

)WITH (OIDS=FALSE);

 

CREATE TABLE coll_fp_subdtls_01(

  CONSTRAINT coll_fp_subdtls_01_pkey PRIMARY KEY (rrid)

)INHERITS (coll_fp_submission_details)

WITH (OIDS=FALSE)

TABLESPACE fpsdts01;

 

CREATE TABLE coll_fp_subdtls_02(

  CONSTRAINT coll_fp_subdtls_02_pkey PRIMARY KEY (rrid)

)INHERITS (coll_fp_submission_details)

WITH (OIDS=FALSE)

TABLESPACE fpsdts02;

 

 

 

In the trigger 

 

CREATE OR REPLACE FUNCTION ins_submission_details()

RETURNS TRIGGER AS $$

  DECLARE

  dateTable TEXT;

  cmd TEXT;

BEGIN

 

  IF ((NEW.rrid % 2)= 0) THEN

dateTable := 'coll_fp_subdtls_01';

  ELSE

dateTable := 'coll_fp_subdtls_02';

 

  END IF;

 

  cmd := 'INSERT INTO ' || dateTable  ||
'(rrid,sid,pfid,timestamp,schema_version,details,app_txn_id)' ||

' VALUES (' ||  quote_literal(NEW.rrid) || ',' ||

quote_literal(NEW.sid) || ',' ||

quote_literal(NEW.pfid) || ',' ||

quote_literal(NEW.timestamp) || ',' ||

quote_literal(NEW.schema_version) || ',' ||

quote_literal(NEW.details) || ',' ||

quote_literal(NEW.app_txn_id) || ')';

 

  EXECUTE cmd;

  RETURN NEW;

END;

  $$LANGUAGE 'plpgsql';

 

 

If I changed the RETURN NEW to RETURN NULL its inserting only one row.

But to work with hibernate I need the return NEW statement.

 

 

Please help me in resolving this.

 

Thanks in advance,

Sridhar ratna

 

 

 

-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org]
On Behalf Of Richard Huxton
Sent: Wednesday, September 09, 2009 3:35 PM
To: Sridhar Reddy Ratna
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] trigger failed to identify the partions

 

Sridhar Reddy Ratna wrote:

> 

> dateTable := coll_fp_subdtls_01;

> 

> ELSE

> 

> dateTable := coll_fp_subdtls_02;

 

 

> ERROR:  column "coll_fp_subdtls_01" does not exist

> 

> ERROR: column "coll_fp_subdtls_01" does not exist

 

 

I think you missed the word "column" in the error message (easy to do,

you know you are naming tables). You've missed the quotes around the

partition-names so it's trying to find a column on a table that matches.

 

  dateTable := 'coll_fp_subdtls_01';

 

 

-- 

  Richard Huxton

  Archonet Ltd

 

-- 

Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)

To make changes to your subscription:

http://www.postgresql.org/mailpref/pgsql-sql


__

DISCLAIMER

The information contained in this e-mail message and/or attachments to it may
contain confidential or privileged information. If you are not the intended
recipient, any dissemination, use, review, distribution, printing or copying
of the information contained in this e-mail message and/or attachments to it
are strictly prohibited. If you have received this communication in error,
please notify us by reply e-mail or directly to netsupp...@cmcltd.com or
telephone and immediately and permanently delete the message and any
attachments. Thank you.


__

This email has been scrubbed for your protection by SecureMX.
For more information visit http://securemx.in
__

[SQL] Differences between bit string constant sintax

[Oliveiros C,]

Dear All,

I have a table which has a field that is of type bit varying.

When I do a direct INSERT with , say, X'1F', everything
works fine.

But in this table I have to insert several tens of thousands
of records each time, so I decided to use
COPY command.

And if I build a file like this (columns separated by tab char)
primary_key1X'1F'
primary_key2X'1FF'
...

The system will complain on the first line,
saying that ' is not an hexadecimal digit.

I tried to remove the single quotes and it worked fine...
With values like this : X1F, X1FF, etc

Is the system supposed to behave like this, or have
I gone sideways somewhere on this?

My copy command is just this :

COPY t_unique_browsers 
("IDUniqueBrowsers","browsersSet") 
 FROM $file$C:\temp\pg\totalAccount.sql$file$;

Also,
SELECT version() says :
"PostgreSQL 8.3.7, compiled by Visual C++ build 1400"

Thanx in advance for your help

Best,
Oliveiros

[SQL] how to: refer to select list calculations other places in the calculations.

[Little, Douglas]

Hi,
I have very complex sql (generated by cognos) I'm porting from Teradata that 
basically uses column alias in the calculated columns.
Is there any way to do this type of thing in Psql?

Essentially I need to do is refer to a calculated column later in the select 
list (but far more complex - like 50 pages printed out)
sel a,b,a+b as c, c*d, c*f, c*g, c*h, c*I, c*j, c*k,..   ---  where c 
is a calculated column used in other calculations.
from 


Doug Little
Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com

 [cid:image002.jpg@01CA3127.61E69F30]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

Re: [SQL] trigger failed to identify the partions

[Richard Huxton]

Sridhar Reddy Ratna wrote:
> 
>   cmd := 'INSERT INTO ' || dateTable  ||

>   EXECUTE cmd;

>   RETURN NEW;

> If I changed the RETURN NEW to RETURN NULL its inserting only one row.

Yes. RETURN NEW allows the insert to procede normally so you end up with
the two rows.

> But to work with hibernate I need the return NEW statement.

Hmm - not sure I can see a way around this. Maybe someone else who knows
Hibernate can help.

-- 
  Richard Huxton
  Archonet Ltd

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql