Re: [SQL] Create function statement with insert statement
Hello Susan,
>
> The relevant code for creating the hexorders table (and associated
> constraints) is:
>
>
> DROP TABLE HEXORDERS ;
>
> DROP SEQUENCE HEXORDERS_SEQ ;
>
> CREATE SEQUENCE HEXORDERS_SEQ START 1 ;
>
> CREATE TABLE HEXORDERS (
>ORDER_ID INTEGER DEFAULT NEXTVAL('HEXORDERS_SEQ') NOT NULL,
>CUSTOMER_ID INTEGER NOT NULL,
>ORDER_AMOUNT NUMERIC(12,2),
>ORDER_DISCOUNT_CODE CHARACTER(1),
>ORDER_KEY VARCHAR(255),
>DISTRIBUTOR_ID INTEGER,
>ORDER_GST NUMERIC(12,2),
>ORDER_SHIPPING_COST NUMERIC(12,2),
>ORDER_DATE DATE DEFAULT CURRENT_DATE,
>ORDER_VALID BOOLEAN DEFAULT 'FALSE',
>ORDER_SHIPPING_DATE DATE,
>ORDER_DELIVERY_DATETIME TIMESTAMP,
>ORDER_FREIGHT_COMPANY VARCHAR(30),
>ORDER_CLOSE_DATE DATE );
>
>
> ALTER TABLE HEXORDERS ADD CONSTRAINT HEXORDERS_CONSTRAINT_PK
> PRIMARY KEY ( ORDER_ID );
>
>
> ALTER TABLE HEXORDERS ADD CONSTRAINT
> HEXORDERS_CONSTRAINT_FK1 FOREIGN KEY ( CUSTOMER_ID ) REFERENCES
> HEXCUSTOMERS ( CUSTOMER_ID ) MATCH FULL ;
>
>
> ALTER TABLE HEXORDERS ADD CONSTRAINT HEXORDERS_CONSTRAINT_FK2
> FOREIGN KEY ( DISTRIBUTOR_ID ) REFERENCES HEXDISTRIBUTORS
> ( DISTRIBUTOR_ID ) MATCH FULL ;
>
Within my postgres environment
PostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.2
I've created table HEXORDERS and added HEXORDERS_CONSTRAINT_PK,
and did not add HEXORDERS_CONSTRAINT_FK1 nor HEXORDERS_CONSTRAINT_FK2,
because of no idea how HEXCUSTOMERS resp. HEXDISTRIBUTORS look like.
Then I did successfully
CREATE OR REPLACE FUNCTION orderinsert(INTEGER, VARCHAR) RETURNS
INTEGER AS '
INSERT INTO HEXORDERS ( CUSTOMER_ID, ORDER_KEY, DISTRIBUTOR_ID,
ORDER_AMOUNT, ORDER_GST ) VALUES ( $1, $2, 1, 0, 0 ) ;
SELECT 1 ;
' LANGUAGE SQL ;
Even a
SELECT orderinsert( 123,'abcdef' );
worked as intended (one row inserted).
Nothing about "parse error at or near ;"
So you find me pretty clueless about what's going wrong on your side.
Did you search the archives for hints on strange parser errors?
Regards, Christoph
PS Keep on posting to the list, maybe somebody else knows more.
---(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
[SQL]
unsubscribe end ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] View - Join based on dis-similar data types
Not sure if this ever made it to the group, I can't seem to find it in the recent message lists. "speakeasy" <[EMAIL PROTECTED]> wrote in message news:... > I have a field defined as a character(50) data type, and the same field > stored in a transition table as a text type. > > The view itself work based on the join, however sub-queries against the view > on that field do not return any data. > > Example: > > Table1 > > T1Data - Character(50) > > > Table2 > > T2Data - Text > > > View1: > --- > SELECT T1Data from Table1 JOIN Table2 ON (Table1.T1Data = Table2.T2Data); > > > A select against View1 > SELECT * FROM View1 returns all relevant records, however, adding a WHERE > clause produces no output. > > Please advise. > > ---(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
[SQL] Poor performance on a right join
When doing a join query I am getting a responce time of 3 seconds. The callhist table has 66000 rows and the phone table has 1 rows. I have an indexes on callhist.call_date, callhist.operator_id, phone.phone, & phone.cust_code. Here's the SQL SELECT a.CALL_DATE, a.OPERATOR_ID, a.CUST_CODE FROM PHONE as b right join CALLHIST as a on (a.CUST_CODE=b.CUST_CODE) where (b.PHONE = '555-555-') order by a.call_date desc; The postgres db is running on a 2 P3 700 processor server with 1GB of ram running Red Hat Linux 7.3. I am running PostgreSQL 7.2.2 Why is the query taking so long? What can I do to help the performance? Thanks in advance, Carmen ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] nearest match
> Ryan wrote: >> I'm doing some work with part diagrams and server-side image maps. I >> want to store single point coordinates (x,y) for reference numbers in >> a table looking like: >> >> reference_number text, >> x int, >> y int >> >> My question is: How can I find the *nearest* match of some clicked on >> coordinates without specifying some arbitrary distance from the stored >> point? > > How about something like this? > > select reference_number, '(50,50)'::point <-> point(x,y) as distance > from my_table order by distance limit 1; > > With a reasonably small table, it should perform fairly well. Hot damn! thats exactly what I needed. I imagine I would only be comparing the distance of 50 points at any given time (about the max number of reference numbers on any given image) so its mighty quick. (184 points takes 1.80 msec to check) I didn't even know about those geometric operators (I find new stuff every day I swear). Are they SQL standard or postgres specific? Ryan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Create function statement with insert statement
I think the language needs to be in quotes ... ... ' language 'sql'; >>> "Jon Griffin" <[EMAIL PROTECTED]> 03/12/03 11:59AM >>> You need to put your aliases in: value1 alias for $1; etc. Hello, > > I am trying to create a database trigger which inserts into a second > table. I have created the following function in accordance with the > reference manual documentation (7.2). > > > CREATE OR REPLACE FUNCTION orderinsert(INTEGER, VARCHAR) RETURNS INTEGER > AS ' > > INSERT INTO HEXORDERS ( CUSTOMER_ID, ORDER_KEY, DISTRIBUTOR_ID, > ORDER_AMOUNT, ORDER_GST ) VALUES ( $1, $2, 1, 0, 0 ) ; > > SELECT 1 ; > > ' LANGUAGE SQL ; > > > > It fails with the cryptic error "parse error at or near ;". Can anyone > tell me what is wrong with this syntax? > > Regards ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] nearest match
--On Thursday, March 13, 2003 11:22:21 -0600 Ryan Orth <[EMAIL PROTECTED]> wrote: Ryan wrote: Hot damn! thats exactly what I needed. I imagine I would only be comparing the distance of 50 points at any given time (about the max number of reference numbers on any given image) so its mighty quick. (184 points takes 1.80 msec to check) I didn't even know about those geometric operators (I find new stuff every day I swear). Are they SQL standard or postgres specific? postgres specific. There are a WHOLE bunch of geometric operators and functions in PG. LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(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
Re: [SQL] What's wrong with this group by clause?
[forwarding to -hackers]
On Tue, 4 Nov 2003 18:28:12 -0300, Franco Bruno Borghesi
<[EMAIL PROTECTED]> wrote:
>Below you can find a simplified example of a real case.
>I don't understand why I'm getting the "john" record twice.
ISTM you have found a Postgres 7.3 bug.
I get one john with
PostgreSQL 7.1.3 on i686-pc-cygwin, compiled by GCC 2.95.3-5
and
PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.7.2.1
but two johns with
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.7.2.1
>/*EXAMPLE*/
>CREATE TABLE people
>(
> name TEXT
>);
>INSERT INTO people VALUES ('john');
>INSERT INTO people VALUES ('john');
>INSERT INTO people VALUES ('pete');
>INSERT INTO people VALUES ('pete');
>INSERT INTO people VALUES ('ernest');
>INSERT INTO people VALUES ('john');
>
>SELECT
> 0 AS field1,
> 0 AS field2,
> name
>FROM
> people
>GROUP BY
> field1,
> field2,
> name;
>
> field1 | field2 | name
>++
> 0 | 0 | john
> 0 | 0 | pete
> 0 | 0 | ernest
> 0 | 0 | john
>(4 rows)
Same for
SELECT 0 AS field1, 0 AS field2, name
FROM people
GROUP BY 1, 2, name;
Servus
Manfred
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [SQL] What's wrong with this group by clause?
>
> On Tue, 4 Nov 2003 18:28:12 -0300, Franco Bruno Borghesi
> <[EMAIL PROTECTED]> wrote:
> >Below you can find a simplified example of a real case.
> >I don't understand why I'm getting the "john" record twice.
>
> ISTM you have found a Postgres 7.3 bug.
>
> I get one john with
> PostgreSQL 7.1.3 on i686-pc-cygwin, compiled by GCC 2.95.3-5
> and
> PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.7.2.1
>
> but two johns with
> PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.7.2.1
>
> >/*EXAMPLE*/
> >CREATE TABLE people
> >(
> > name TEXT
> >);
> >INSERT INTO people VALUES ('john');
> >INSERT INTO people VALUES ('john');
> >INSERT INTO people VALUES ('pete');
> >INSERT INTO people VALUES ('pete');
> >INSERT INTO people VALUES ('ernest');
> >INSERT INTO people VALUES ('john');
> >
> >SELECT
> > 0 AS field1,
> > 0 AS field2,
> > name
> >FROM
> > people
> >GROUP BY
> > field1,
> > field2,
> > name;
> >
> > field1 | field2 | name
> >++
> > 0 | 0 | john
> > 0 | 0 | pete
> > 0 | 0 | ernest
> > 0 | 0 | john
> >(4 rows)
>
PostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.2
SELECT 0 AS field1, 0 AS field2,name FROM people GROUP BY
field1, field2, name;
field1 | field2 | name
++
0 | 0 | ernest
0 | 0 | john
0 | 0 | pete
(3 rows)
PostgreSQL 7.3.2 on hppa-hp-hpux10.20, compiled by GCC 2.95.2
SELECT 0 AS field1, 0 AS field2,name FROM people GROUP BY
field1, field2, name;
field1 | field2 | name
++
0 | 0 | john
0 | 0 | pete
0 | 0 | john
0 | 0 | pete
0 | 0 | john
0 | 0 | ernest
(6 rows)
I doubt this is a bug in 7.3.2 but in prior versions.
I've cross-checked how another DBMS (HP's ALLBASE) handles GROUP BY
without an aggregate, and it acts like 7.3.2.
Regards, Christoph
---(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
Re: [HACKERS] [SQL] What's wrong with this group by clause?
Manfred Koizar <[EMAIL PROTECTED]> writes:
> ISTM you have found a Postgres 7.3 bug.
Yeah. Actually, the planner bug has been there a long time, but it was
only latent until the parser stopped suppressing duplicate GROUP BY
items:
2002-08-18 14:46 tgl
* src/backend/parser/parse_clause.c: Remove optimization whereby
parser would make only one sort-list entry when two equal()
targetlist items were to be added to an ORDER BY or DISTINCT list.
Although indeed this would make sorting fractionally faster by
sometimes saving a comparison, it confuses the heck out of later
stages of processing, because it makes it look like the user wrote
DISTINCT ON rather than DISTINCT. Bug reported by
[EMAIL PROTECTED]
7.3 patch is attached if you need it.
regards, tom lane
*** src/backend/optimizer/plan/planner.c.orig Wed Mar 5 13:38:26 2003
--- src/backend/optimizer/plan/planner.cThu Mar 13 11:21:16 2003
***
*** 1498,1510
* are just dummies with no extra execution cost.)
*/
List *sort_tlist = new_unsorted_tlist(subplan->targetlist);
int keyno = 0;
List *gl;
foreach(gl, groupClause)
{
GroupClause *grpcl = (GroupClause *) lfirst(gl);
! TargetEntry *te = nth(grpColIdx[keyno] - 1, sort_tlist);
Resdom *resdom = te->resdom;
/*
--- 1498,1511
* are just dummies with no extra execution cost.)
*/
List *sort_tlist = new_unsorted_tlist(subplan->targetlist);
+ int grpno = 0;
int keyno = 0;
List *gl;
foreach(gl, groupClause)
{
GroupClause *grpcl = (GroupClause *) lfirst(gl);
! TargetEntry *te = nth(grpColIdx[grpno] - 1, sort_tlist);
Resdom *resdom = te->resdom;
/*
***
*** 1518,1523
--- 1519,1525
resdom->reskey = ++keyno;
resdom->reskeyop = grpcl->sortop;
}
+ grpno++;
}
Assert(keyno > 0);
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] What's wrong with this group by clause?
From: Franco Bruno Borghesi <[EMAIL PROTECTED]> >SELECT > 0 AS field1, > 0 AS field2, >name >FROM > people >GROUP BY > field1, > field2, >name; I think the problem is that you don't have a column to group on. Try adding SELECT ,count(*) so that there is an aggregate of some kind. Alternatively, you could use DISTINCT ON (field1,field2) field1,field2,name FROM ... although this is a Postgres specific extension of the SQL spec. Len Morgan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] displaying correct name
Bruce Young wrote: what i want to do is select from "requests" where ownerid=. the result should contain the users.username of requests.buyerid and the item.title of requests.itemid. my problem is... i am getting the username of requests.ownerid instead from my query. here is my query: select u.username, i.title from test.requests r, test.users u, test.items i where r.ownerid=u.userid and r.itemid=i.itemid and r.ownerid = (select userid from test.users where userid=1); thanks. any help appreciated. - bruce ??? As you answered yourself in description above, change r.ownerid=u.userid into r.buyerid=u.userid Regards, Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
