[SQL] Bug in JDBC CREATE FUNCTION syntax?
I'm trying to do a create function using JDBC 3,0 in Eclipse IDE with JFaceDBC plugin. This function doesn't work: CREATE FUNCTION @[EMAIL PROTECTED]@[EMAIL PROTECTED] () RETURNS boolean LANGUAGE SQL AS ' ALTER TABLE @[EMAIL PROTECTED]@[EMAIL PROTECTED] ADD CONSTRAINT @[EMAIL PROTECTED]@[EMAIL PROTECTED] PRIMARY KEY (batch) ; ALTER TABLE @[EMAIL PROTECTED]@[EMAIL PROTECTED] ADD CONSTRAINT @[EMAIL PROTECTED]@[EMAIL PROTECTED] UNIQUE (batch, id) ; ALTER TABLE @[EMAIL PROTECTED]@[EMAIL PROTECTED] ADD CONSTRAINT @[EMAIL PROTECTED]@[EMAIL PROTECTED] PRIMARY KEY (batch, id, class) ; SELECT true;' ; But the same, written using a different indetation protocol, works: CREATE FUNCTION @[EMAIL PROTECTED]@[EMAIL PROTECTED] () RETURNS boolean LANGUAGE SQL AS ' ALTER TABLE @[EMAIL PROTECTED]@[EMAIL PROTECTED] ADD CONSTRAINT @[EMAIL PROTECTED]@[EMAIL PROTECTED] PRIMARY KEY (batch); ALTER TABLE @[EMAIL PROTECTED]@[EMAIL PROTECTED] ADD CONSTRAINT @[EMAIL PROTECTED]@[EMAIL PROTECTED] UNIQUE (batch, id); ALTER TABLE @[EMAIL PROTECTED]@[EMAIL PROTECTED] ADD CONSTRAINT @[EMAIL PROTECTED]@[EMAIL PROTECTED] PRIMARY KEY (batch, id, class) ; SELECT true;' ; It seem's that the problem is about the semi-colon position. The following query MUST be on the same line to make it works... Does anyone else notice the same thing? Thanks, -- Ci-git une signature avortee. ** RIP ** ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Bug in JDBC CREATE FUNCTION syntax?
Sorry for this misplaced question. Olivier Hubaut wrote: [snip] -- Ci-git une signature avortee. ** RIP ** ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] How to completely move a table to another schema?
Hi all; I have a function which moves a table from one schema to another by updating the relnamespace field of pg_class: CREATE OR REPLACE FUNCTION move_relation(VARCHAR, VARCHAR, VARCHAR) RETURNS BOOL AS ' -- $1 is the table name -- $2 is the source schema -- $3 is the destination schema -- UPDATE pg_catalog.pg_class SET relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = $3) WHERE relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = $2) AND relname = $1; UPDATE pg_catalog.pg_type SET typnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = $3) WHERE typnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = $2) AND typname = $1; SELECT TRUE; ' LANGUAGE SQL; Am I missing anything? I have already had a few problems that led me to discover that I needed to put in the second update query. Just figured I would check. Best Wishes, Chris Travers ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] first of month
Jeff,
We use a simple function that can be called in any query, like so:
SELECT * FROM table WHERE column < first_of_month();
-- Function: public.first_of_month()
CREATE FUNCTION public.first_of_month() RETURNS date AS '
DECLARE stamp DATE;
BEGIN
SELECT INTO stamp CAST(date_trunc(''month'',now())AS DATE);
return stamp;
END;' LANGUAGE 'plpgsql' STABLE;
--
Bill MacArthur
Webmaster
The DHS Club, Inc.
The Best Is Yet To Come!
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] How to specify the beginning of the month in Postgres SQL syntax?
"sqllist" <[EMAIL PROTECTED]> writes:
> WHERE date < to_date( to_char(current_date,'-MM') || '-01','-mm-dd')
That seems like the hard way. Try this:
regression=# select date_trunc('month', now());
date_trunc
2003-12-01 00:00:00-05
(1 row)
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to completely move a table to another schema?
Chris Travers <[EMAIL PROTECTED]> writes: > I have a function which moves a table from one schema to another by > updating the relnamespace field of pg_class: > Am I missing anything? pg_constraint entries related to the table. Also you need to recurse for each index of the table. regards, tom lane ---(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
[SQL] picking max from list
I have a query that produces results similar to this: run# rd_pk group# 0 9209 5 1 9209 8 0 9520 2 1 9520 5 0 9520 etc 0 8652 1 8652 2 8652 0 8895 1 8894 Ultimately I want to know the group number for EACH rd_pk with the highest run number. Can this be done in one query? Or will I need to code with a loop? Thanks Jodi -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
Re: [SQL] picking max from list
On Wed, Dec 10, 2003 at 15:37:10 -0500, Jodi Kanter <[EMAIL PROTECTED]> wrote: > I have a query that produces results similar to this: > > run# rd_pk group# > 09209 5 > 19209 8 > 09520 2 > 19520 5 > 09520 etc > 08652 > 18652 > 28652 > 08895 > 18894 > > Ultimately I want to know the group number for EACH rd_pk with the > highest run number. Can this be done in one query? Or will I need to > code with a loop? > Thanks You can do this using the postgres distinct on extension. The query would look something like: select distinct on (rd_pk) run, rd_pk, group from table_name order by run desc; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] picking max from list
select group,rd_pk from (select ...) as your_query, (select max(run) as max_run,rd_pk as rd from (select ...) as your_query) as max_rd where rd = rd_pk and max_run = run; I dont know if you call that one query but it should work. There may be more elegant solutions, but I havn't had a chance to read up on the new join types. Good luck Jodi Kanter wrote: I have a query that produces results similar to this: run# rd_pk group# 09209 5 19209 8 09520 2 19520 5 09520 etc 08652 18652 28652 08895 18894 Ultimately I want to know the group number for EACH rd_pk with the highest run number. Can this be done in one query? Or will I need to code with a loop? Thanks Jodi -- Guy Fraser Network Administrator ---(end of broadcast)--- TIP 8: explain analyze is your friend
