[GENERAL] pl/pgsql string combining

[Bino Oetomo]

Dear All

I have 2 table :
1. hotel_pbx_country
2. hotel_pbx_area

Country is one2many to area
Area have a field called prefx

The prefx field is auto filled by country.code and area.code
and for that purpose, i created trigger and function
Trigger-
CREATE TRIGGER prefx_xtrigger
   BEFORE INSERT OR UPDATE
   ON hotel_pbx_area
   FOR EACH ROW
   EXECUTE PROCEDURE prefx_xupdate();
function
CREATE OR REPLACE FUNCTION prefx_xupdate()
   RETURNS trigger AS $$
   DECLARE ctrcode VARCHAR ;
   BEGIN
   select code into ctrcode from hotel_pbx_country where id = 
NEW.country_id ;

   NEW.prefx = ctrcode || NEW.code;
   RETURN NEW;
   END;
$$ LANGUAGE 'plpgsql';
---EOF---

I make a bulk entry ,
Not all area have area.code .. it's not mandatory

In normal condition where both country.code and area.code is not null 
 the trigger is work great.
But the problem come when area.code is null, it cause area.prefx to be 
NULL also.



Is it normal behavior ? or is it my fault ?

Kindly please give me your enlightment on how to fix it

Sincerely
-bino-

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

Re: [GENERAL] pl/pgsql string combining

[Bino Oetomo]

Dear Mr. Stehule

Thankyou for your super prompt (came to my mailbox less then 2 minutes 
since my post) enlightment.


I'll try it

Sincerely
-bino-

Pavel Stehule wrote:

Hello

NULL and any is NULL. So you have to use coalesce function.

like

NEW.prefix = ctrcode || coalesce(NEW.code, '');

Regards
Pavel Stehule

  



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

Re: [GENERAL] How is the right query for this condition ?

[Bino Oetomo]

Harald Fuchs wrote:



For larger tables where an index search would be useful, check out
pgfoundry.org/projects/prefix:

...
  

Wow ... yet another enlightment

Thankyou, I realy appreciate

Sincerely
-bino-

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

[GENERAL] pgsql 'prefix' error

[Bino Oetomo]

Dear All
Harald Fuchs wrote:

For larger tables where an index search would be useful, check out
pgfoundry.org/projects/prefix:


CREATE TABLE myrecords (
  record prefix_range NOT NULL,
  PRIMARY KEY (record)
);

COPY myrecords (record) FROM stdin;
1
12
123
1234
\.

  

I downloaded pgfoundry's prefix, postgresql-8.3-prefix_1.0.0-1_i386.deb
I install it using dpkg , and run the prefix.sql

Create database .. named 'prefbino', and

CREATE TABLE myrecords (
 record prefix_range NOT NULL,
 PRIMARY KEY (record)
);


Looks good, next
I try to create some records, But I got this error :
---START--
prefbino=# COPY myrecords (record) FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
 1
 12
 123
 1234
 \.
ERROR: duplicate key value violates unique constraint myrecords_pkey
CONTEXT: COPY myrecords, line 2: 12

---STOP--

Kindly please give me further enlightment

Sincerely
-bino-

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

Re: [GENERAL] pgsql 'prefix' error

[Bino Oetomo]

Dear Harald
Harald Fuchs wrote:

At least in prefix 1.0.0 unique indexes seem to be broken.  Just drop
the primary key and add a separate index:

CREATE INDEX myrecords_record_ix ON myrecords USING gist (record);


  


Yup .. it works now.

Thankyou for your enlightment

Sincerely
-bino-

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

[GENERAL] How is the right query for this condition ?

[Bino Oetomo]

Dear All

Suppose I created a database with single table like this :
--start--
CREATE DATABASE bino;
CREATE TABLE myrecords(record text);
--end

and I fill myrecords with this :
--start--
COPY myrecords (record) FROM stdin;
1
12
123
1234
\.
--end

In my bash script, I have variable called 'vseek', that will be use for 
query parameter.

How to query the table , for (i.e):

a. If vseek = '127' , I want the result is == '12'
b. if vseek = '123987' , I want the result is == '123'
c. if vseek = '14789' , I want the result is == '1'

Kindly please give me any enlightment

Sincerely
-bino-


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

Re: [GENERAL] How is the right query for this condition ?

[Bino Oetomo]

Dear Sir
Brian Modra wrote:

You can use a plpgsql to do that e.g.

create or replace function getMatchingRecord(vseek text)
returns text as $$
declare
 str text;
 len integer;
 ret text;
  

...

I Just try your solution , and it's work like a charm

Thankyou for your enlightment

Sincerely
-bino-

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