[SQL] unicode(utf-8) problem !
the chinese utf-8 code haves 3 bytes When I use the query command Like " select * from phpbb_users where username='阿至' " the Result is Too Much Records! :-( user_id=9522, username= 阿è²user_id=8261, username= 阿超user_id=6364, username= 阿è°user_id=6141, username= 阿è²user_id=5490, username= 阿苹user_id=4694, username= é˜¿è »user_id=2618, username= 阿虎user_id=1979, username= 阿至
[SQL] two records per row from query
mytable pkid class_date. sessionid select * from mytable 1 2009/01/01 2101 2 2009/01/02 2101 I would like an SQL that would produce newtable pkid, class_date1, class_date2, sessionid1, sessionid2 Select * from newtable 1 2009/01/01 2009/01/02 2101 2101 I have a list of classes that is perfect for our needs. However, I need to create the second table (from a query) to feed to a report writer so it can write out a single line of text for two records. Like: Your class dates are as follows Date Date 01/01/2009 01/02/2009 01/08/2009 01/10/2009 03/31/2009 04/05/2009 and will continue until the all the classes are printed. The problem of course is the table has a row per class and the report writer needs two class dates per row. I have no idea how to do this using SQL. Thanks in advance, Johnf -- 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] two records per row from query
On Wednesday 05 August 2009 10:21:08 pm A. Kretschmer wrote: > In response to John : > > mytable > > pkid > > class_date. > > sessionid > > > > select * from mytable > > 1 2009/01/01 2101 > > 2 2009/01/02 2101 > > > > I would like an SQL that would produce > > > > newtable > > pkid, > > class_date1, > > class_date2, > > sessionid1, > > sessionid2 > > > > Select * from newtable > > > > 1 2009/01/01 2009/01/02 2101 2101 > > I will try, but i'm not sure if i understand you correctly. Your table > contains only 2 rows and both rows contains the same sessionid. Can i > use that sessionid to find the rows that belongs together? > > Okay, my table: > > test=*# select * from mytable ; > pkid | class_date | sessionid > --++--- > 1 | 2009-01-01 | 2101 > 2 | 2009-01-02 | 2101 > 3 | 2009-02-01 | 2102 > 4 | 2009-02-02 | 2102 > 5 | 2009-03-01 | 2103 > 6 | 2009-03-02 | 2103 > (6 rows) > > > As you can see, there are 3 different sessionid's. > > test=*# select distinct on (sessionid1,sessionid2) pkid, classdate1, > classdate2, sessionid1, sessionid2 from (select least(a.pkid, b.pkid) as > pkid, least(a.class_date, b.class_date) as classdate1, > greatest(a.class_date, b.class_date) as classdate2, a.sessionid as > sessionid1, b.sessionid as sessionid2 from mytable a inner join mytable > b on (a.sessionid=b.sessionid)) foo order by sessionid1, > sessionid2,pkid; > pkid | classdate1 | classdate2 | sessionid1 | sessionid2 > --++++ > 1 | 2009-01-01 | 2009-01-01 | 2101 | 2101 > 3 | 2009-02-01 | 2009-02-01 | 2102 | 2102 > 5 | 2009-03-01 | 2009-03-01 | 2103 | 2103 > (3 rows) > > > Hope that helps... > > > Andreas Thanks - the sessionid's in fact do match. It's just that I can have more than two (2) classes per sessionid. So mytable might look like: select * from mytable 1 2009/01/01 2101 2 2009/01/02 2101 3 2009/02/05 2101 4 2009/02/15 2101 5 2009/02/25 2101 I will try to use your solution. I was also looking at using an array aggregate. I'm not sure how I use it but it might work. Also I'm using 8.3.7 if that helps. Johnf -- 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] two records per row from query
On Thursday 06 August 2009 06:42:34 am Leo Mannhart wrote: > John wrote: > > mytable > > pkid > > class_date. > > sessionid > > > > select * from mytable > > 1 2009/01/01 2101 > > 2 2009/01/02 2101 > > > > I would like an SQL that would produce > > > > newtable > > pkid, > > class_date1, > > class_date2, > > sessionid1, > > sessionid2 > > > > Select * from newtable > > > > 1 2009/01/01 2009/01/02 2101 2101 > > > > I have a list of classes that is perfect for our needs. However, I need > > to create the second table (from a query) to feed to a report writer so > > it can write out a single line of text for two records. > > Like: > > > > Your class dates are as follows > > > >Date Date > > 01/01/2009 01/02/2009 > > 01/08/2009 01/10/2009 > > 03/31/2009 04/05/2009 > > and will continue until the all the classes are printed. > > > > The problem of course is the table has a row per class and the report > > writer needs two class dates per row. > > > > I have no idea how to do this using SQL. > > > > Thanks in advance, > > Johnf > > Can you give a more precise example please? I don't get what you really > need. What I understand is that you want 1 record back for each > sessionid with the earliest and latest class_date. > > I've done the following: > > lem=# select * from mytable; > pkid | class_date | sessionid > --+-+--- > 1 | 2009-01-01 00:00:00 | 2101 > 2 | 2009-01-02 00:00:00 | 2101 > 3 | 2009-01-01 00:00:00 | 2102 > 4 | 2009-01-02 00:00:00 | 2102 > 5 | 2009-01-01 00:00:00 | 2103 > 6 | 2009-01-02 00:00:00 | 2103 > 7 | 2009-01-03 00:00:00 | 2103 > (7 rows) > > and then: > > lem=# select min(pkid) as pkid > lem-# ,min(class_date) as class_date1 > lem-# ,max(class_date) as class_date2 > lem-# ,sessionid > lem-# from mytable > lem-# group by sessionid; > pkid | class_date1 | class_date2 | sessionid > --+-+-+--- > 5 | 2009-01-01 00:00:00 | 2009-01-03 00:00:00 | 2103 > 3 | 2009-01-01 00:00:00 | 2009-01-02 00:00:00 | 2102 > 1 | 2009-01-01 00:00:00 | 2009-01-02 00:00:00 | 2101 > (3 rows) > > Is this what you need or is there something else? Can you give more > sample data and the result you expect from it? > > > Cheers, Leo I'm sorry I was attempting to simplify the problem. I will attempt to provide more info: OVERVIEW: "mytable" contains the dates of the classes a student will attend along with fields to identify the student (not really it's normalized). One row per class. In general the student signs up for a session. A session has many classes that run for some length of time. Normally, a few months. Classes maybe on some set schedule or not. Maybe on each Saturday and Sunday for two months - maybe a total of 16 classes. What I need is a way to gather the classes two (maybe three) at a time into one row. I need this because the report writer processes the data one row at a time. And I need the report writer to print two class dates on one line of the report. So the output would look similar to the follows on the report: Your class schedule is as follows: Saturday 01/03/2009 Sunday 01/04/2009 Saturday 01/10/2009 Sunday 01/11/2009 Saturday 01/17/2009 Sunday 01/18/2009 And of course the schedule will continue until all the classes are print. Also note that the dates are in order from left to right and then down. THE PROBLEM: Since the classes are in a single row per class I need a way to get two classes into a single row to allow the report writer to print two classes per row. I don't know how too! In general the sessionid will be the same but it is not the only thing I'm using to find the student. The "essess" table is the available sessions. The "esclass" contains the classes and any reschedule classes with a FK into essess The 'esenroll' has the student, the session. This is converted from an old Visual Fox Pro program. The actual tables in question The sessions: CREATE TABLE essess ( pkid serial NOT NULL, sessionid_do_not_use integer, courseid integer, instrid integer, sequenceid integer, began date, ended date, cancelled boolean, name_1 character varying(35), locationid integer, facility character varying(35), availseats numeric(5), depart integer, stop_close boolean DEFAULT false, langid integer,
Re: [SQL] two records per row from query
On Friday 07 August 2009 02:50:48 am Leo Mannhart wrote: > John wrote: > [snip] > > > I'm sorry I was attempting to simplify the problem. I will attempt to > > provide more info: > > > > OVERVIEW: > > "mytable" contains the dates of the classes a student will attend along > > with fields to identify the student (not really it's normalized). One > > row per class. In general the student signs up for a session. A session > > has many classes that run for some length of time. Normally, a few > > months. Classes maybe on some set schedule or not. Maybe on each > > Saturday and Sunday for two months - maybe a total of 16 classes. > > > > What I need is a way to gather the classes two (maybe three) at a time > > into one row. I need this because the report writer processes the data > > one row at a time. And I need the report writer to print two class dates > > on one line of the report. > > > > So the output would look similar to the follows on the report: > > > > Your class schedule is as follows: > > > > Saturday 01/03/2009 Sunday 01/04/2009 > > Saturday 01/10/2009 Sunday 01/11/2009 > > Saturday 01/17/2009 Sunday 01/18/2009 > > > > And of course the schedule will continue until all the classes are print. > > Also note that the dates are in order from left to right and then down. > > [snip] > > I hope I understand now. > I can not give you a pure SQL solution, where you only have a single > select. For this, I'm missing things like analytic-functions and > subquery-factoring in PostgreSQL. I'm coming from Oracle where it would > be easier for me. > Nevertheless, I'll give you here my way to get the result. > > I have: > > lem=# select * from mytable; > pkid | class_date | sessionid > --+-+--- > 1 | 2009-01-01 00:00:00 | 2101 > 2 | 2009-01-02 00:00:00 | 2101 > 3 | 2009-01-01 00:00:00 | 2102 > 4 | 2009-01-02 00:00:00 | 2102 > 5 | 2009-01-01 00:00:00 | 2103 > 6 | 2009-01-02 00:00:00 | 2103 > 7 | 2009-01-03 00:00:00 | 2103 > 8 | 2009-01-08 00:00:00 | 2101 > 9 | 2009-01-09 00:00:00 | 2101 >10 | 2009-01-15 00:00:00 | 2101 >11 | 2009-01-03 00:00:00 | 2102 >12 | 2009-01-08 00:00:00 | 2102 >13 | 2009-03-01 00:00:00 | 2104 >14 | 2009-03-02 00:00:00 | 2104 >15 | 2009-03-03 00:00:00 | 2104 >16 | 2009-03-08 00:00:00 | 2104 >17 | 2009-03-09 00:00:00 | 2104 >18 | 2009-03-10 00:00:00 | 2104 >19 | 2009-03-15 00:00:00 | 2104 >20 | 2009-03-16 00:00:00 | 2104 >21 | 2009-04-01 00:00:00 | 2105 >22 | 2009-04-02 00:00:00 | 2105 >23 | 2009-04-03 00:00:00 | 2105 >24 | 2009-04-08 00:00:00 | 2105 >25 | 2009-04-09 00:00:00 | 2105 >26 | 2009-04-10 00:00:00 | 2105 >27 | 2009-04-15 00:00:00 | 2105 > (27 rows) > > lem=# > > and this is what I get: > > lem=# \i q1.sql > BEGIN > CREATE SEQUENCE > CREATE SEQUENCE > SELECT > class_date1 | sessionid1 | class_date2 | sessionid2 > ---++---+ > Thursday 01-JAN-2009 | 2101 | Friday02-JAN-2009 | 2101 > Thursday 08-JAN-2009 | 2101 | Friday09-JAN-2009 | 2101 > Thursday 15-JAN-2009 | 2101 | | > Thursday 01-JAN-2009 | 2102 | Friday02-JAN-2009 | 2102 > Saturday 03-JAN-2009 | 2102 | Thursday 08-JAN-2009 | 2102 > Thursday 01-JAN-2009 | 2103 | Friday02-JAN-2009 | 2103 > Saturday 03-JAN-2009 | 2103 | | > Sunday01-MAR-2009 | 2104 | Monday02-MAR-2009 | 2104 > Tuesday 03-MAR-2009 | 2104 | Sunday08-MAR-2009 | 2104 > Monday09-MAR-2009 | 2104 | Tuesday 10-MAR-2009 | 2104 > Sunday15-MAR-2009 | 2104 | Monday16-MAR-2009 | 2104 > Wednesday 01-APR-2009 | 2105 | Thursday 02-APR-2009 | 2105 > Friday03-APR-2009 | 2105 | Wednesday 08-APR-2009 | 2105 > Thursday 09-APR-2009 | 2105 | Friday10-APR-2009 | 2105 > Wednesday 15-APR-2009 | 2105 | | > (15 rows) > > ROLLBACK > lem=# > > my q1.sql-file looks like this, though you can play around: > > begin; > create sequence mytable_seq; > create sequence myreport_seq; > create temp table myreport on commit
[SQL] trouble with getting the field names
Below is a SQL statement that was created to retreive the primary key , and
column names and data types for a table name and a schema. The problem is
that is works very well if I only use the 'public' schema. But it does not
work if I use a 'system' schema I created (owned my me). The offending line
is "AND pg_table_is_visible(c.oid)" which makes me believe I done something
wrong with the roles? IOW if I drop the "pg_table_is_visible" it works with
my 'system' schema. So I need a better guru than myself to help/tell me what
I did wrong. Thanks in advance!
SELECT a.attname, t.typname,
EXISTS(SELECT * FROM generate_series(0, 31) idx(n)
WHERE a.attnum = i.indkey[idx.n]) AS isprimary
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid
JOIN pg_type t ON t.oid = a.atttypid LEFT
JOIN pg_index i ON i.indrelid = c.oid AND i.indisprimary
WHERE c.relname = 'sys_company' AND n.nspname = 'system'
AND a.attname NOT IN ('ctid', 'cmin', 'cmax', 'tableoid', 'xmax', 'xmin')
AND has_schema_privilege(n.oid, 'usage')
AND has_table_privilege(c.oid, 'select')
AND pg_table_is_visible(c.oid) ORDER BY c.relname, a.attname
Johnf
--
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] trouble with getting the field names
On Monday 26 October 2009 04:41:49 pm John wrote:
> Below is a SQL statement that was created to retreive the primary key , and
> column names and data types for a table name and a schema. The problem is
> that is works very well if I only use the 'public' schema. But it does not
> work if I use a 'system' schema I created (owned my me). The offending
> line is "AND pg_table_is_visible(c.oid)" which makes me believe I done
> something wrong with the roles? IOW if I drop the "pg_table_is_visible" it
> works with my 'system' schema. So I need a better guru than myself to
> help/tell me what I did wrong. Thanks in advance!
>
> SELECT a.attname, t.typname,
> EXISTS(SELECT * FROM generate_series(0, 31) idx(n)
> WHERE a.attnum = i.indkey[idx.n]) AS isprimary
> FROM pg_class c
> JOIN pg_namespace n ON n.oid = c.relnamespace
> JOIN pg_attribute a ON a.attrelid = c.oid
> JOIN pg_type t ON t.oid = a.atttypid LEFT
> JOIN pg_index i ON i.indrelid = c.oid AND i.indisprimary
> WHERE c.relname = 'sys_company' AND n.nspname = 'system'
> AND a.attname NOT IN ('ctid', 'cmin', 'cmax', 'tableoid', 'xmax', 'xmin')
> AND has_schema_privilege(n.oid, 'usage')
> AND has_table_privilege(c.oid, 'select')
> AND pg_table_is_visible(c.oid) ORDER BY c.relname, a.attname
>
> Johnf
OK I discovered the problem I needed to add
… ALTER USER test SET search_path TO schema1,schema2
thanks
Johnf
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] determine the curval() of a view
Hi, I am working with a view that has a "_INSERT" rule. I'm wondering if there is a way to determine what the curval() of the PK (serial type) after an insert occurs. Given the name of the view I'd like to determine the sequence associated with the view's PK. Actually, I wonder if it is even possible to determine the PK from the view name. I'm guessing that it might be possible because the view must be associated with a table/s. And they will have PK's. Any hints will be very helpful. Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] understanding select into
Hi, I am reviewing a function written by some xTuple guys. What is interesting about it is it uses the "INTO" statement like select something into _p from sometable where somecriteria. The function contiunes and uses the data retreived _p.somefield_name And then the function ends. Ok my question: I also thought the select "into" created a real table. But after running the function the table does not exist. I see no where that a 'drop' is issued. In fact the function uses lot's of select into's like (_test, _r, etc..). So would some kind soul explain what is happening. Could it be that "_p" is drop automaticly when the function ends? Something to do with scope. Could it have something to do with the fact the function returns only an integer? And that causes the table to be drop. As you can see I'm lost here! Johnf -- 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] understanding select into
Wow thanks to all that replied - you folks are correct. The "_p" and the others are vars. _p RECORD; I won't forget that one for a long time (however, I do drink :-)) Johnf On Friday 09 April 2010 10:32:51 am Pavel Stehule wrote: > Hello > > 2010/4/9 John : > > Hi, > > I am reviewing a function written by some xTuple guys. What is > > interesting about it is it uses the "INTO" statement like > > > > select something into _p from sometable where somecriteria. > > > > The function contiunes and uses the data retreived > > _p.somefield_name > > > > And then the function ends. > > > > > > Ok my question: > > > > I also thought the select "into" created a real table. But after running > > the function the table does not exist. I see no where that a 'drop' is > > issued. In fact the function uses lot's of select into's like (_test, _r, > > etc..). So would some kind soul explain what is happening. > > _p is record variable. See some lines before. There will be DECLARE part > > DECLARE p RECORD; > > There is plpgsql's SELECT INTO and SQL's SELECT INTO with little bit > different syntax. First - target is list of variables or record > variable, second - target is table. > > > Could it be that "_p" is drop automaticly when the function ends? > > Something to do with scope. > > _p is just variable > > regards > Pavel Stehule > > > Could it have something to do with the fact the function returns only an > > integer? And that causes the table to be drop. > > > > As you can see I'm lost here! > > > > > > Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] what does this do
Hi,
I have a piece of python code that excutes a SQL statement:
apitempCur.execute("select * from jfcs_balancedue('%s') f(enrolleeid varchar,
course_cost decimal, paid_amt decimal)" % (enrollIds,));
The "enrollids" is a list of primary keys and the "jfcs_balancedue" is a user
defined function. What I don't understand is the "f(enrolleeid
varchar, ...)" I have no idea what it's for? Would some kind soul educate
me.
Thanks in advance,
Jhnf
--
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] what does this do
On Thursday 10 June 2010 06:46:46 am Little, Douglas wrote:
> First remove the python
> select * from jfcs_balancedue(parameter) f(enrolleeid varchar,course_cost
> decimal, paid_amt decimal)
>
> the jfcs_balancedue is a table function, f is the alias (with the column
> alias list/datatype of the columns returned by the function).
>
> Doug
>
>
> -Original Message-
> From: pgsql-sql-ow...@postgresql.org
> [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of John Sent: Thursday,
> June 10, 2010 4:22 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] what does this do
>
> Hi,
> I have a piece of python code that excutes a SQL statement:
>
> apitempCur.execute("select * from jfcs_balancedue('%s') f(enrolleeid
> varchar, course_cost decimal, paid_amt decimal)" % (enrollIds,));
>
> The "enrollids" is a list of primary keys and the "jfcs_balancedue" is a
> user defined function. What I don't understand is the "f(enrolleeid
> varchar, ...)" I have no idea what it's for? Would some kind soul
> educate me.
>
> Thanks in advance,
> Jhnf
Thanks folks the link you folks provided did the trick
"If the function has been defined as returning the record data type, then an
alias or the key word AS must be present, followed by a column"
I was not aware of the syntax required for returning the record data type.
Johnf
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] is there a tutorial on window functions?
Hi,
I'd like to learn the use of window functions and did not find a tutorial
using google ("postgres window function tutorial"). I'm hoping someone has a
link.
Johnf
--
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] is there a tutorial on window functions?
On Friday 11 June 2010 07:37:29 am Andreas Kretschmer wrote:
> John wrote:
> > Hi,
> > I'd like to learn the use of window functions and did not find a tutorial
> > using google ("postgres window function tutorial"). I'm hoping someone
> > has a link.
>
> My link-collection: http://delicious.com/akretschmer/windowing and
> http://delicious.com/akretschmer/cte
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknown)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Thanks all
Johnf
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] strangest thing happened
I am the only developer, DBA etc.. for a small project. Today (yesterday was everything was perfect) many of the sequence numbers fell behind what is the actual PK value. For example the invoice PK sequence current value = 1056 but the table PK was 1071. Nobody (other than myself) knows how to edit/access the postgres server. So 1. Does anyone know how this could have happened?? Other than human interaction. 2. Does anyone have a script to reset the sequences to match the tables? Thanks in advance, Johnf -- 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] strangest thing happened
Yes I'm using auto_increment (serial data type). No function, method inserts
PK's anywhere in my code. I'm thinking/guessing it had something to do with
vacumn or the backup. I have been using Postgres for a number of years now
and I never seen this in the past. So I'm really at a loss as how this could
have occurred. The backup is a windows product "exec" and I'm using a
special plug-in from exec for the Linux backup. But I still can't see this
actually happening.
Well I have it running for the moment and I'll have to account black magic as
the cause.
Johnf
On Wednesday 07 July 2010 02:25:13 pm Justin Graf wrote:
> Are you using PG's sequence/auto increment???
>
> If so.
> Once PG fires off the nextval() for the sequence that number is
> considered used and gone even if the transaction that called nextval()
> is rolled back
>
> Depending on how the app is written nextval() might be called, but allow
> the User to cancel the invoice creation before the insert into table is
> completed eating up Invoice numbers
>
> To reset Sequences number call
> Select setval('Sequence_Name', VAlue_To_Set_To);
>
> Most people ignore this kind of annoyance when sequence numbers jump.
> Now if it happens all the time where every X hours eating up Z number of
> sequence numbers then one needs to dig into the logs and figure out what
> is calling nextval()
>
> Search the logs to see what is calling nextval('My_Sequence')
>
> You may need to turn up logging to find it.
>
> On 7/7/2010 2:59 PM, John wrote:
> > I am the only developer, DBA etc.. for a small project. Today (yesterday
> > was everything was perfect) many of the sequence numbers fell behind what
> > is the actual PK value. For example the invoice PK sequence current
> > value = 1056 but the table PK was 1071. Nobody (other than myself) knows
> > how to edit/access the postgres server. So
> >
> > 1. Does anyone know how this could have happened?? Other than human
> > interaction.
> >
> > 2. Does anyone have a script to reset the sequences to match the tables?
> >
> > Thanks in advance,
> >
> > Johnf
--
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] strangest thing happened
On Wednesday 07 July 2010 03:14:40 pm Justin Graf wrote: > I would be looking at the log files for the Inserts into that table as a > means to track down what is the cause. If there are no log files or > don't have enough detail, crank up the logging level and wait for it to > happen again??? That is scary - let it happen again I'm not keeping enough info in the log. I actually turned off most of the info the log files are gathering because the system has been running for 6-7 months without an issue. I just got a call around noon telling me something was going wrong. That's when I discovered the sequences were the wrong values. I'm sure there has to be some sort of real explanation - but I don't know what it is. Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Need Help With Dates.
Hello. I just migrated a database from MySQL to postgreSQL and am having trouble wit postgres' dates. MySQL dealt with dates very well, but i don't see the same sort of functionality in postgres. The database is an archive of imformation, and i would like to do a cron'd select for an interval based on the date. I can get the current date. But i don't know how to have the computer properly figure out the past dates. The select format has been: SELECT blah FROM blah2 WHERE date BETWEEN (past_date) and (current_date); This select is computed monthly. And i do not want to have to change the variables every month when this needs to run. Nor do i think that i should have to result to perl processing to solve this dilemma. I have tried (i think) every possible function and operation to try to get this to work. The problem is trying to figure out whether an extra day should be added for leap years. (It obviously should, but how do i tell the computer that it should). Postgres does not seem to recognize that concept well. Since this is running monthly, if you have any ideas to form a select like: SELECT _ WHERE date BETWEEN (date - 12 months) and ... in other words, since postgres increments by day . . . is there anyway to get it to allow you to increment / decrement by month? thanks in advance. also for reading this (long winded) post. .jtp
[SQL] confused by select.
Hello. I'm trying to do a select here that i have looked at from many angles and cannot find a solution too. My main problem, (i believe) is that it is trying to create a many to many relationship. I would be grateful if anyone knew a way around this. Here's my predicamint. I have a database for sales orders. An inventory table. And A history table. Inventory: Create t1 (sku char(4), type char(1)); History: Create t2 (id char(6), items text); [There are more fields, but this is all that matters for this query] I would like to get the id's where the customer has purchased an item of a specific type. Problem A: most people order more than one item at a time. So the 'items' field is a colon delimitted text field containing the skus of the purchased items. Problem B: there are many skus of each type. as are there many purchases. What would the proper select be? create view v1 (select sku from t1 where type ='K'); will get me all the skus of one type but i don't know where to go from there. And it feels as if i've exhausted all options. i've been working around: select id from t2 where items like sku; and no matter what i use in the where clause (regex, like, or wildcards). i get back an error or a zero. and there are no other related fields in the mentioned tables. is there a way to step through the sku field item by item without leaving postgres (i.e. resorting to scripting)? I have also tried different fieldtypes for the 'items' field. But they all give me problems too. The array works much the same way as the : delimitted field i have does. Except you have less operators that work with it. And to break it up into separate items fields. (item1, item2, item3, etc.) is a waste, seeing as the average order is 2.? but there are many orders with hundreds of items. Sorry for the long winded explanation. But I figured, that the more imformation i gave, the more someone may be able to help. Thanks in advance. .jtp
[SQL] Nested Aggregates?
I know nested aggregates aren't allowed, or at least not implicitly. Is there a way to get around this.? Or does someone feel like droppng some other hints this way? Problem : (not really) I have a table (id, date, ordertype, etc...) Based on the type i want to be able to get the counts of how many people have ordered from a certain type of ordertype. so i. select id, count(*) as cnt from T1 where ordertype = 'Q' group by id; This will give me the number of times each id has ordered type Q. I need a way to find out how many times, or how many ids have ordered type Q once, twice, thrice, etc. I can accomplish this by doing a select into temp with the above statement. And then doing. Select cnt, count(*) from TEMP group by cnt; This really doesn't seem the most efficient way to do this by me. And i know nested aggs aren't allowed (or don't like me). And i'm trying to fidn a way to eliminate the insert / select step. so the main question is. how do i accomplish ths better?can anyone help? and how can i implement it as a subselect that will work? will that be more efficient. Sorry if the question's seem simple / trivial. But it's been gnawing at me for a while that this doesn't seem to be the most efficient way to handle this select. And i can't figure out another way that postgres will accept. Thanks in advance. .jtp
Re: [SQL] Select subset of rows
On Sun, 27 Aug 2000, Stephan Szabo wrote: > > Of course immediately after sending the last message and logging off my > ISP I figured out the simpler way for the third one: > > begin; > select salary into temp saltemp from employee order by salary desc > limit 5; > select name from employee where exists (select * from saltemp where > saltemp.salary=employee.salary); > end; > > Stephan Szabo > [EMAIL PROTECTED] > I wonder if the following might be a bit faster? Or would it be slower? select salary into temp saltemp from employee order by salary desc limit 5; select min(salary) as minsal into test minsal from saltemp; select name, salary from employee, minsal where salary >= minsal; I don't know the speed difference between my second select and your subselect within an EXISTS clause might be. Just a thought, John
Re: [SQL] Week of the Year?
On Sun, 3 Sep 2000, Brian C. Doyle wrote:
> John,
>
> Would you have any clue how to figure out the first saturday of any month -
> 6 days and the last saturday of that month?
>
> I know that this seems odd but i have to run reports for "Non Standard
> Months" and well I am clueless.
>
> At 09:55 PM 8/11/00 -0500, you wrote:
>
Actually, I'm glad you wanted Saturday and not some other day of the week,
it's simplier. I'm hope you don't mind some "tutorial" type language. I
usually do this when I want to try to explain my thought processes (such
as they are). It's not meant to be "talking down" to anybody. So let's
think about this for a second. We know that the first Saturday of the
month must be in the range from the 1st day of the month to the 7th day of
the month. We also know that we can determine the "day of week" by using
the date_part("dow",datevar). The "day of week" function returns a number
from 0 (for Sunday) to 6 (for Saturday). My first thought was to create a
table containing dates. It would have the 1st through the 7th of every
month, along with the corresonding day of the week. I could then do
something like:
select datevar from dates where weekday='Saturday' and
date_part('month',datevar)=8 /* where 8 is for August */
This works, but it's really a pain since I need a big table containing all
these dates. So I changed my approach. I then thought, OK, I want the
first Saturday after the 1st of the month. So, how many days
must I add to the 1st of the month it to get to Saturday? Well, if it is
already Saturday (dow==6), then 0. If Friday (dow==5), then 1. And so on.
That's when the light really went on. That's just
SELECT test_date+(6-date_part('dow',test_date)) AS Saturday FROM
test_table;
Now in the original question is 6 days before the first Saturday of the
month. So just subtract 6 from the above.
The last Saturday of the month is similiar. In fact, the last Saturday of
a month is simply 7 days before the first Saturday of the *next* month. So
if you want the last Saturday of October, then find the first Saturday of
November and subtract 7.
Hope this gets you going.
John
Re: [SQL] sum of agreggates in one SELECT?
Well, it's not a single SELECT, but why not use something like: SELECT MAX(b.lot) AS quanity, max(p.price) AS price, p.login INTO TEMPORARY TABLE temp1 FROM bid b, person p WHERE b.auction_id=84 AND p.id=b.person_id GROUP BY p.login ORDER BY max(price); SELECT SUM(quanity) from temp1; If you need the output from the original SELECT then you can print it by simply doing: SELECT * FROM temp1; Hope this is of some use to you, John On Tue, 19 Sep 2000, Louis-David Mitterrand wrote: > Hello, > > I have the following query/result: > > auction=# select max(b.lot) as quantity,max(b.price) as price,p.login from bid b, person p where b.auction_id = 84 and p.id = b.person_id group by p.login order by max(price); > quantity | price | login > --+---+--- > 1 | 5000 | papy >12 | 5750 | cunctator > 8 | 6000 | vindex > (3 rows) > > Now I would like to sum() all results from the quantity column and > return it with one SELECT statement. Is that possible? > > I am trying: > > auction=# select sum(b.lot) from bid b, person p where b.auction_id = 84 and p.id = >b.person_id ; > sum > - > 52 > (1 row) > > But this is wrong because it sums all quantities. I don't know how to > apply a valid WHERE clause in that case. > > Thanks in advance for any help, cheers, > > >
Re: [SQL] memory usage
Carolyn Wong writes: > This program seems to use a lot of the memory on the linux server, and > the memory doesn't seem to be released at the end of execution. Are you quite certain that this is actually what is happening? Linux memory usage can be confusing. -- John Hasler [EMAIL PROTECTED] (John Hasler) Dancing Horse Hill Elmwood, WI
Re: [SQL] -query sql
On Tue, 3 Oct 2000, Nema, Vivek wrote: > Hi! >a small query may be u can help me. > i just wanted to compare 2 columns in 2 tables.how can i do it in sql > statement.i know it is possible somehow i am not able to write my query. > i am using RDB 6.0 > > Any pointer or help will be highly appreciated. > What is RDB? This list is for PostgreSQL. However, in general, suppose you have column1 in table1 and column2 in table2, you can select rows where they are equal by doing something like: SELECT table1.column1, table1.another, table2.thisone, FROM table1, table2 WHERE table1.column1=table2.column2; Change the information you want in the first line of the SELECT. I just made up some names. Hope this helps, John
Re: [SQL] if else query help
On Thu, 12 Oct 2000, Jean-Christophe Boggio wrote: > portion of the day (although I don't know --yet-- how to convert > date2-date1 to an integer, trunc does not work). reltime(date2-date1)::int Will subtract date1 from date2, then cast it to an integer. John
Re: [SQL] nested transactions
Bernie Huang writes: > Just out of curiousity, does Postgres support nested transactions? I'd like to know too, and not just out of curiousity. I have a use for that. -- John Hasler [EMAIL PROTECTED] (John Hasler) Dancing Horse Hill Elmwood, WI
Re: [SQL] postgres
Joseph Shraibman writes: > I've been wondering for a long time how people manage to find the mailing > list without finding the web site. They do a Web search on 'postgres' and get a zillion hits on articles in the list archive. They then look at the first article and pull the address out of that. They never notice where the article came from. -- John Hasler [EMAIL PROTECTED] Dancing Horse Hill Elmwood, Wisconsin
Re: [SQL] Invoice number
Mike Castle writes: > If so, why is no rollbackable an issue? All you should need is unique > numbers. Not necessarily exactly sequential numbers. Sometimes business rules require that every member of a sequence of such things as invoice numbers be accounted for. Speculation: Would it be possible to log SERIAL's as they are issued? It might be sufficient to just record the user id, though it would be more useful to log some indication of what the number was used for (or at least whether or not it was used at all). -- John Hasler [EMAIL PROTECTED] (John Hasler) Dancing Horse Hill Elmwood, WI
Re: [SQL] Invoice number
Mike Castle writes: > Client 3 comes along. Do they use invoice #1, out of order, or invoice > #3? It shouldn't matter, as long as every number is accounted for. Seems to me that a trigger could make a log entry every time the serial is incremented. Workable? > What happens in a paper world if a cup of coffee is spilt on some > invoices, and these precious items are thrown in the trash? They are returned to accounting with an explanatory note, the numbers are logged as "voided", and the spoiled forms are shredded. -- John Hasler [EMAIL PROTECTED] Dancing Horse Hill Elmwood, Wisconsin
[SQL] Re: system catalog info
hi Ron, Try the developers manual: http://www.postgresql.org/devel-corner/docs/postgres/developer.htm actual catalog info is: http://postgresql.mirror.aarnet.edu.au/devel-corner/docs/postgres/catalogs.htm If you find any other sources please let me know - especially any that refer to composite types ;-) cheers, John Ron Peterson wrote: > > The HTML programming documentation (e.g. > http://www.postgresql.org/devel-corner/docs/programmer/pg-system-catalogs.htm) > indicates that more extensive information about the system catalogs can > be found in the "Reference Manual". Where can this reference manual be > found? Or where can more extensive documentation about the system > catalogues be found? > > -Ron- -- -- john reid e-mail [EMAIL PROTECTED] technical officerroom G02, building 41 school of geosciences phone +61 02 4221 3963 university of wollongong fax +61 02 4221 4250 uproot your questions from their ground and the dangling roots will be seen. more questions! -mentat zensufi apply standard disclaimers as desired... --
[SQL] abstract data types?
Hi all,
I'm sure this has become somewhat of a FAQ recently, but I couldn't
find any reference to casting composite types in the mailing lists.
I'm trying to figure out what support PostgreSQL offers for SQL99
abstract data types.
PostgreSQL version is cvs from about a week ago.
Given the following relations:
test=> \d c_adtattr
Table "c_adtattr"
Attribute | Type | Modifier
---+--+--
attr1 | text |
attr2 | text |
test=> \d c_adtparent
Table "c_adtparent"
Attribute | Type| Modifier
---+---+--
basetype | text |
adtattr | c_adtattr |
OK, now try and insert a tuple into the relation with the composite
attribute:
test=> INSERT INTO c_adtparent values ('basetype','{"adtr1a1","adtr1a2"}');
ERROR: Attribute 'adtattr' is of type 'c_adtattr' but expression is of
type 'unknown'
You will need to rewrite or cast the expression
Is this use of the '{" delimiters correct? I took it from the array
docs, which was only reference that I could find to anything like
inserting values into composite types.
OK, try something stupid (cast as pg_type.typname):
test=> INSERT INTO c_adtparent values ('basetype', CAST
('{"adtr1a1","adtr1a2"}' AS c_adtattr));
ERROR: pg_atoi: error in "{"adtr1a1","adtr1a2"}": can't parse
"{"adtr1a1","adtr1a2"}"
OK, try insert into individual attributes:
test=> INSERT INTO c_adtparent (basetype, adtattr.attr1, adtattr.attr2)
VALUES ('basetype', CAST ('adtr1a1') AS text, CAST ('adtr1a2') AS text);
ERROR: parser: parse error at or near "."
OK, try double dot syntax from SQL99:
test=> INSERT INTO c_adtparent (basetype, adtattr..attr1,
adtattr..attr2) VALUES ('basetype', CAST ('adtr1a1') AS text, CAST
('adtr1a2') AS text);
ERROR: parser: parse error at or near "."
So far, so bad. Am I doing something really stupid with the syntax,
should I be using a different approach, or does the support not yet
exist? If it's just my stupidity, can someone please give me some
pointers to exactly where I should RTFM?
OK, so why am I attempting this lunacy? I am interested in assisting
with the development of a data store for GIS. Looks like most of the
mooted schemas will involve the creation of large numbers of tables of
identical type. Each of these in turn contains possibly repeating
identical conceptual structures. So to me it made sense, rather than
producing application specific code to manage these, to see if support
could be added to the backend DBMS, so that other applications (e.g.
CAD) could make use of the functionality.
TIA for any assistance.
cheers,
John
--
--
john reid e-mail [EMAIL PROTECTED]
uproot your questions from their ground and the dangling roots will be
seen. more questions!
-mentat zensufi
apply standard disclaimers as desired...
--
[SQL] DATE
PLEASE ADVISE HOW I SHOULD ALTER THE COMMAND: <$NOW;DD;> TO GIVE ME A DATE THAT IS X DAYS FORWARD ie: I WISH TO REPRESENT A DATE IN FORM WHICH IS A 7 DAYS FORWARD OF THE DATE NOW. REGARDS JOHN WHALE
[SQL] Re: abstract data types?
Hi Tom, listers, Thanks for the info. > On Sat, 20 Jan 2001, Tom Lane wrote: > > >> None, I fear. The stuff you are fooling with is leftover from the old >> PostQuel language. Most of it is suffering from bit rot, because the >> developers' focus has been on SQL92 compliance for the last six or seven >> years. > Damn! Not what I wanted to hear :-( >> I hadn't realized that SQL99 had caught up to PostQuel in this >> area ;-). > FWIW, this is actually one of the primary reasons that I became interested in PostgreSQL, before I even knew about SQL3/SQL99. Seems like such a cool idea :-) >> Sounds like we will have to dust off some of that stuff and >> get it working again. No promises about timeframe, unless someone >> steps up to the plate to do the work... > OK, what few coding skills I had are so rusty I'm pretty much back to square one, but I would like to help out where possible (Docs maybe?). Then again, might as well jump in the deep end, and have a look to see what needs doing anyway :-) Can you please give me some pointers as to where I should look in the docs and code to see how classes are currently handled. I'm thinking specifically of: * How (and where) the access methods for class tuples are implemented and called. * Where the code for creating classes hides * Anything else that I should be aware of! For the moment I guess I don't need to worry about the parser, just how the operations related to the classes (both system and user) work/are implemented. Correct? > What goes around comes around. :-) And hits you in the back of the head just when you least expect it ... cheers, John -- john reid e-mail [EMAIL PROTECTED] uproot your questions from their ground and the dangling roots will be seen. more questions! -mentat zensufi apply standard disclaimers as desired... --
[SQL] Re: abstract data types?
Hi Josh et al, Sorry for the tardy reply, and thanks for your comments. Any suggestions or pointers on robust database design will be greatly appreciated. Josh Berkus wrote: > Jim, > > >>> I'm trying to figure out what support PostgreSQL >> >> offers for SQL99 >> >>> abstract data types. >> > I'm a little curious why what you're attempting couldn't be > done with two columns rather than inventing your own data > type. As somebody who often rescues databases gone bad, > composite data types have not earned a warm place in my > heart ... > > -Josh Berkus What we are attempting is the storage of vector data for a geographical (or spatial) information system in a database. We hope to base the implementation on the upcoming standard from the ISO TC/211 committee. More information can be found at http://FMaps.sourceforge.net/ - the webpages need a major revamp so the best place to look for current developments is in the mailing list archive. A good source of info can be found at http://gdal.velocet.ca/projects/osvecdb/index.html, especially relevant is the comparison of the SQL/MM, OGC, and ISO TC/211 standards (http://gdal.velocet.ca/projects/osvecdb/comp-mm-ogc-tc211.pdf ). To answer your question, it is a bit hard to say at the moment as the design schema for our project has only just been started. The draft versions of the ISO standard that I have seen use an object oriented data model, so to me it makes sense to try and keep the database schema as close as possible to this (minimise data impedance). Briefly, at its' simplest the schema will probably use a two tier approach. Tier 0ne -- The original data stored in the most flexible way that we can think of, with associated metadata tables. Tier Two --- These will effectively be persistent views on the T1 tables structured for efficient access by client applications. OK, as far I know no such beast as a persistent view exists in the SQL standards, but that is probably the best way to describe what I have in mind. Using views as currently implemented in PostgreSQL would probably not be viable as it is likely that, if multiple spatial reference systems are defined on a area of interest, reprojection of the geometry objects would be a performance killer. cheers, John
[SQL] Re: abstract data types?
problems for implementing abstract data types that I can see so far: * Inheritance is currently implemented at the relation level, rather than the type level. Is this simply a matter of changing the references in pg_inherits from pg_class.oid to pg_type.oid? Or would this cause major breakages in other parts of the code? * The existing "CREATE TABLE tablename AS" syntax is incompatible (or needs to be modified to comply) with the SQL99 syntax of "CREATE TABLE tablename AS typename"; * Code for creating a composite attribute member currently implements them as a oid referencing a seperate table. According to Date this is probably "not a Good Thing" (see [DAT00] Section 25.2 pg 865) - in this case relvar = object class rather than his preferred domain = object class. I assume the methods necessary to read and write complex attributes would be similar in nature to those employed for table access - correct? Oh, well. Back to tracing how procedures are called from the system catalogs I guess. From a previous post of mine: "Can you please give me some pointers as to where I should look in the docs and code to see how classes are currently handled. I'm thinking specifically of: * How (and where) the access methods for class tuples are implemented and called. * Where the code for creating classes hides * Anything else that I should be aware of! For the moment I guess I don't need to worry about the parser, just how the operations related to the classes (both system and user) work/are implemented. Correct?" Any help people can give me would be much appreciated. I'm already feeling a little lost. I hope people don't mind if I ask a lot of dumb questions over the next few weeks :-) Is this the appropriate list, or should I move over to hackers? Cheers, John Where I'm getting my info from: Book [Dat00] Author: Date, C.J. Title: An Introduction to Database Systems Publisher: Addison Wesley Longman Date: 2000 Edition: 7th Book [DD00] Author: Date, C.J.; Darwen, Hugh Title: Foundation for Future Database Systems : the Third Manifesto Publisher: Addison Wesley Date: 2000 Edition: 2nd Book [SB99] Author:Stonebraker, Michael; Brown, Paul Title: Object-Relational DBMSs : Tracking the Next Great Wave Publisher: Morgan Kaufmann Date: 1999 Edition: 2nd Book [For99] Author:Fortier, Paul Title: SQL3 Implementing the SQL Foundation Standard Publisher: McGraw Hill Date: 1999 -- john reid e-mail [EMAIL PROTECTED] uproot your questions from their ground and the dangling roots will be seen. more questions! -mentat zensufi apply standard disclaimers as desired... --
[SQL] Re: abstract data types?
Hi again, Josh Berkus wrote: > John, > > > Thanks for your comments. My 2c worth: > > That was at least $1.50 worth. Teach me to speak 'off the > cuff' on this list ... Just because I went out and brought a stack of books doesn't mean that I actually know anything ;-) > > As > > far as the > > relationship between the schemas for financial and > > spatial information > > systems goes, a book I have (on OO database management) > > goes so far as > > to say "that relational database systems do not > > adequately support these > > so-called non-standard applications." > > I'd agree with you, I'm afraid. Most of the "spatial > database projects" I've been familiar with involved either: > a) completely custom software, or b) *lots* of RAM and > processing power, or c) both. These are some of the things that have me scared - actually these considerations are main reason that I was was thinking of a two-tier approach. The data "views" the applications would access directly would be optimised for performance, the underlying store for flexible storage and data integrity. I figure big disks are a lot cheaper than a dirty great machine. Especially if I can use IDE RAID and run on otherwise throwaway hardware - we don't need 100% uptime, just need to make sure we don't loose the base data. The ability to get it all running again in several hours would be a definite plus as well! > > Unfortunately I can't speak from personal > > experience - I > > don't have any access to it, as at uni we are a Oracle/MS > > SQL > > Server/mySQL shop, and from my preliminary investigations > > none of these > > seem to cut it for this task as far as I am concerned :-( > > A definite No for 2 of the above. MySQL was built to be > fast and light, with a minimal feature set. As a > semi-certified MS SQL Admin, I can tell you that MS SQL > Server isn't up to anything better than a *simple* > accounting database. Oracle, on the other hand, claims to > do anything. They really have no geometic support? Oracle does have geometric support (Spatial Data Cartridge I think it's called). My main concern after reading the Oracle8 technical reference was the underlying fundamentals. From what I could see, Oracle seems to have just slapped an object-relational type syntax over the original relational engine. IIRC, all datatypes were still rigidly structured i.e. fixed length arrays support only, no variable length data types etc. For any application trying to model the vagaries of the "real" world, I feel that this can only lead to tears (or a tendency for DBA's to go insane) sooner or later - probably sooner. BTW, if any insomniacs out there are looking for a cure, try reading the O8TR manual. I can recall falling asleep after about 1 page just after consuming about 3 cups of strong coffee - which would normally have me bouncing off ceilings :-) > > Interesting. This is a really cool site. Thanks. However > > I don't see how > > you draw the conclusion from what I have read on this > > site "that > > object-oriented and relational approaches to data > > problems *cannot* be > > made to reconcile." C.J. Date here seems to be arguing > > more about the > > semantics employed in UML modelling, Pascal more about > > the quality of > > database design. This site does give me the urge to read > > up on set > > theory - I've forgotten what little I once knew. > > You're right, that's what's currently on the site. I'm > basing my opinion more on the earlier writings of Pascal ... > and porbably on my own expereinces. Of course, we could ask > him. > > > In [DAT00] (Section 25.1 pg 863) Date states "we need do > > nothing to the > > relational model in order to achieve object functionality > > in relational > > systems - nothing, that is, except implement it, fully > > and properly, > > which most of today's systems have so signally failed to > > do." > > Yeah. Few systems bother even to fully implement the SQL > standard fully ... and SQL 99 was as much a product of > politics in the computer industry as logic. > > For example, I agree with Pascal & Date that BLOBs are a bad > idea, and a violation of relational priniciples (being data > that cannot be stores as a value in a column in a relation). > One need only look at the terrible and persistent > implementation problems for BLOB support in various > platforms for proof of this. > > > > He then states that "the support is already there [in the > > relational > &
[SQL] Can a function return a record set?
Am I correct in concluding that I can't return a record set from a function? For example, in MS SQL I would do: create procedure foo as select * from yada I expected to be able to do the following in postgresql. create function foo (integer) returns (integer) as ' begin select * from yada; end; ' language 'plpgsql'; I am concluding that I can't. jt
[SQL] Newbie: execute function error!
I created a sample function:
CREATE FUNCTION concat_text (text, text) RETURNS text AS '
BEGIN
RETURN $1 || $2;
END; '
LANGUAGE 'plpgsql';
The creation was fine. But when I execute the function with : select
concat('a', 'b');
I get the error:
NOTICE: plpgsql: ERROR during compile of concat_text near line 1
"RROR: parse error at or near "
What did I do wrong?
Thanks!
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] function to format floats as money?
Ross writes: > But seriously, numeric(10,2) (or whatever precision and scale is correct > for your application) is the standard answer. Floats are fine for money as long as you only add and subtract and don't deal in amounts that won't fit in the mantissa. -- John Hasler [EMAIL PROTECTED] (John Hasler) Dancing Horse Hill Elmwood, WI ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] function to format floats as money?
I wrote: > Floats are fine for money as long as you only add and subtract and don't > deal in amounts that won't fit in the mantissa. Ross writes: > Or you're writing software in Germany (all of the EU now?) that _might_ get > used in an offical capacity. I was referring to what actually works, not to what might or might not meet with the approval of some officialdom or other. The two seldom bear any discernible relationship. -- John Hasler [EMAIL PROTECTED] (John Hasler) Dancing Horse Hill Elmwood, WI ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] unwritable /tmp causes initdb to crash
this is a goofy situtation. we had a new linux 2.4.4 kernel with a /tmp not writable by postgres. granted, this is categorically hosed, but initdb still dumped core under these circumstances. changing the /tmp to writable immediately fixed the problem. -j = John Scott Senior Partner August Associates web: http://www.august.com/~jmscott email: [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Nested Transactions
Can anyone give me an estimate of when we might expect to see nested transactions implemented? -- John Hasler [EMAIL PROTECTED] (John Hasler) Dancing Horse Hill Elmwood, WI ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Is function atomic?
Richard Huxton writes: > All functions take place within a transaction, but since PG doesn't > support nested transactions yet you can't roll back the effects of a > nested function. Do you have any idea when it will? -- John Hasler [EMAIL PROTECTED] (John Hasler) Dancing Horse Hill Elmwood, WI ---(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] Is function atomic?
I wrote: > Do you have any idea when [nested transactions] will [be added]? Richard Huxton writes: > Check the "todo" list in the developers' area on the website - that'll > show what's planned for 7.2 It's listed there: that's why I asked. Is everything on that list planned for 7.2? -- John Hasler [EMAIL PROTECTED] Dancing Horse Hill Elmwood, Wisconsin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Why does this plpgslq always return 1?
Can anyone tell me why this always return 1? Thanks! CREATE FUNCTION passrate(date, date, text) RETURNS float AS ' DECLARE begindate ALIAS FOR $1; enddate ALIAS FOR $2; passfail ALIAS FOR $3; ret float; countp float; counttotal float; BEGIN SELECT INTO countp COUNT(*) FROM benchmark WHERE passfail = passfail; SELECT INTO counttotal COUNT(*) FROM benchmark; ret := countp / counttotal; RETURN ret; END;' LANGUAGE 'plpgsql'; John ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] plpgsql function return multiple values?
Is it possible for a plpgsql function to return a record? I need to return multiple values, and preferably in the form of a record. Thanks in advance! John ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] GUID in postgres
I wrote: > Globally Unique IDentifier, probably. Just hash a 128 bit random number > with the current date. Horst writes: > That gives you no gurantee it will be unique. There is no such guarantee. The probability of a collision due to errors and bugs using a "deterministic" system is sure to be at least as large as the the probability of a chance collision using large random numbers (_random_, not pseudorandom). Stick machine, table, and database ID's in there as well if it makes you more comfortable, but even without them the risk of a collision is down there with the risk of cosmic ray induced errors. _Nothing_, however, can make it zero. > - All tables in need of a global ID _within_ a database inherit a globid > table which contains nothing but an ID of type serial. - When we need > cross-database unique IDs within the same system, the globid table > contains a database identifier as well (like the OID of the pg_database > entry for the database). And that's fine, but the GUID system uses the word "global" in a much more grandiose sense. -- John Hasler [EMAIL PROTECTED] Dancing Horse Hill Elmwood, Wisconsin ---(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] GUID in postgres
Josh writes: > I'm sure you could make your own GUID, whatever one is. Globally Unique IDentifier, probably. Just hash a 128 bit random number with the current date. -- John Hasler [EMAIL PROTECTED] Dancing Horse Hill Elmwood, Wisconsin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
Bruce Momjian writes: > My point is that our current behavior may not be the most intuitive, and > that most people may prefer a change. I would prefer a change. -- John Hasler [EMAIL PROTECTED] Dancing Horse Hill Elmwood, Wisconsin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
Josh Berkus writes:
> now() or now('transaction') returns the transaction timestamp.
> now('statement') returns the statement timestamp now('immediate') returns
> the timestamp at the exact time the function is called.
I like that.
IMHO "the exact time the function is called" is what most people would
expect to get from now(), but it's too late for that.
--
John Hasler
[EMAIL PROTECTED]
Dancing Horse Hill
Elmwood, Wisconsin
---(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] HELP w/ SQL -- distinct select with non distinct fields?
"RVL" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > I'm work with Sybase on the Sun... and, being a clueles newbee in SQL > department, hope you could help. I have a set of data: > > acct name qty link date memo > 101 item_A 100 0001 9/2/02 blah > 101 item_A 250 0001 9/3/02 n/a > 101 item_A80 0002 9/3/02 n/a > 101 item_B90 0002 8/8/02 n/a > 101 item_B 120 0003 9/7/02 n/a > 101 item_B 100 0003 9/2/02 abcd > 102 item_B 100 0004 9/3/02 xyz > 102 item_B 100 0004 9/7/02 xyz > 102 item_C15 0005 9/1/02 n/a > 102 item_C 180 0005 9/5/02 n/a > > I need it to be consolidated by [link] and sorted by [acct] [name] and > subtotaled by [qty]. This is easy if I don't use date and memo: >SELECT DISTINCT acct, name, sum(qty), link FROM item_list >GROUP BY acct, name, link ORDER BY acct, name, line > > acct name qty link > 101 item_A 350 0001 > 101 item_A 170 0002 > 101 item_B 220 0003 > 102 item_B 200 0004 > 102 item_C 195 0005 > > However, I want [date] and [memo] from the _first_ record of the group > to be included. > > acct name qty link date memo > 101 item_A 350 0001 9/2/02 blah > 101 item_A 170 0002 9/3/02 n/a > 101 item_B 220 0003 8/8/02 n/a > 102 item_B 200 0004 9/3/02 xyz > 102 item_C 195 0005 9/1/02 n/a > > Fields [date] and [memo] are not diplicates, so I cannot consolidate > the set if I add them to SELECT. Is there another way to solve this? It helps to supply a CREATE TABLE and an INSERT so that the problem is better defined and a proposed solution can be easily tested. DATE is reserved in SQL so I'll change the column name to the less readable "d". Also, I believe your result is incorrect. CREATE TABLE item_list ( acct INT NOT NULL, name VARCHAR(10) NOT NULL, qty INT NOT NULL, link VARCHAR(5) NOT NULL, d DATETIME NOT NULL, memo VARCHAR(10) NOT NULL, PRIMARY KEY (acct, name, link, d) ) INSERT INTO item_list VALUES (101, ' item_A', 100, '0001', '20020902', 'blah') INSERT INTO item_list VALUES (101, 'item_A', 250, '0001', '20020903', 'n/a') INSERT INTO item_list VALUES (101, 'item_A',80 , '0002', '20020903', 'n/a') INSERT INTO item_list VALUES (101, 'item_B',90, '0002', '20020808', 'n/a') INSERT INTO item_list VALUES (101, 'item_B', 120, '0003', '20020907', 'n/a') INSERT INTO item_list VALUES (101, 'item_B', 100, '0003', '20020902', 'abcd') INSERT INTO item_list VALUES (102, 'item_B', 100, '0004', '20020903', 'xyz') INSERT INTO item_list VALUES (102, 'item_B', 100, '0004', '20020907', 'xyz') INSERT INTO item_list VALUES (102, 'item_C',15, '0005', '20020901', 'n/a') INSERT INTO item_list VALUES (102, 'item_C', 180, '0005', '20020905', 'n/a') SELECT acct, name, SUM(qty) AS total, link, MIN(d) AS first_date, (SELECT memo FROM item_list WHERE acct = i.acct AND name = i.name AND link = i.link AND d = MIN(i.d)) AS first_memo FROM item_list AS i GROUP BY acct, name, link ORDER BY acct, name, link which returns acct nametotal linkfirst_date first_memo 101 item_A 350 0001 2002-09-02 00:00:00.000 blah 101 item_A 80 0002 2002-09-03 00:00:00.000 n/a 101 item_B 90 0002 2002-08-08 00:00:00.000 n/a 101 item_B 220 0003 2002-09-02 00:00:00.000 abcd 102 item_B 200 0004 2002-09-03 00:00:00.000 xyz 102 item_C 195 0005 2002-09-01 00:00:00.000 n/a Regards, jag ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] SELECT statement never completes.
pgsql-sql, We are porting a database from IBM DB2 to PostgreSQL. In several related scripts, there is a SELECT statement that never completes in Postgres but completes in a few seconds using DB2, for example: Table row count: SELECT count(*) FROM tableX; 112671 SELECT count(*) from tableY; 314625 This statement does not complete: SELECT id FROM tableX WHERE id NOT IN (SELECT id FROM tableY); Any suggestions? Thanks __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos & More http://faith.yahoo.com ---(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] NOTICE: generated by sequence nextval()
Postgresql 7.1.3-2
Red Hat 7.2
I've noticed that if I create a sequence, and then
do a select on it using nextval() everything works fine. However, if I drop the
sequence and then create it again, the same select statement generates a
NOTICE.
Is this normal behaviour or a bug? See below.
test=> create sequence
serial;CREATEtest=> select
nextval('serial'); nextval-
1(1 row)
test=> drop sequence
serial;DROPtest=> create sequence serial;CREATEtest=>
select nextval('serial');NOTICE: serial.nextval: sequence was
re-created nextval-
1(1 row)
[SQL]
how to migrate sql from MS sql server to postgresql? i'd like to tranfer sql schema from MS serverExample:***1*if exists (select * from sysobjects where id = object_id(N'[admin].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [admin].[test] go***2*CREATE TABLE [admin].[test] ([test_name] [char] (50) NOT NULL ,[test_value] [int] NOT NULL ) i can only realize part 2. i don't know how to realize part 1.in other words,i want to know how to check if a table exist in postgresql***2* create table test( test_name char (50) not null, test_value int not null ) thanks for any advice!!
[SQL] help!
how to migrate sql from MS sql server to postgresql? i'd like to tranfer sql schema from MS serverExample:***1*if exists (select * from sysobjects where id = object_id(N'[admin].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [admin].[test] go***2*CREATE TABLE [admin].[test] ([test_name] [char] (50) NOT NULL ,[test_value] [int] NOT NULL ) i can only realize part 2. i don't know how to realize part 1.in other words, i want to know how to check if a table exist in postgresql ***2* create table test( test_name char (50) not null, test_value int not null ) thanks for any advice!!
[SQL] hi
i'd like to tranfer sql schema from MS serverExample:***1*if exists (select * from sysobjects where id = object_id(N'[admin].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [admin].[test] go***2*CREATE TABLE [admin].[test] ([test_name] [char] (50) NOT NULL ,[test_value] [int] NOT NULL )i can only realize part 2 and must need the fuction that can check data table. create table test(test_name char (50) not null,test_value int not null)thanks for any advice!!Do You Yahoo!? ÑÅ»¢ÓéÀÖÐÂÏʵ½µ×,µç×ÓÖܱ¨¿ìÀÖµ½¼Ò!
[SQL] CoreReader
Does anybody know of CoreReader being run against PostGresql? If so, I would appreciate knowing the results. -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] CoreReader
I really do need to develop some people skills. At least enough to avoid the ego bruises. CoreReader is a free download at http://corereader.com It does point and click queries. It runs from a windows front end, but is designed to connect to any data source on any computer. It has run against mainframes, spreadsheets, mysql, paradox, etc., but nobody has reported a Postgrsql connection, and I have not had time. It should work fine, but after spending long hours this weekend fixing a bug that manifested only in certain kinds of Oracle connections... > On Fri, Oct 25, 2002 at 10:32:00PM -0500, John Ragan wrote: > > > > Does anybody know of CoreReader being run > > against PostGresql? > > > > If so, I would appreciate knowing the > > results. > > What's CoreReader? > > -Roberto > > -- > +|Roberto Mello -http://www.brasileiro.net/ |--+ > + Computer Science Graduate Student, Utah State University + > + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + > -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] DB2 to Postgresql conversion help.
All, Any suggestions on a Postgresql equivalent to the following DB2 sql query: SELECT * FROM (SELECT ID,NAME,CITY,STATE,PROGRAM,CUST_NAME,CUST_ID,ROWNUMBER() OVER (ORDER BY ID ASC) AS RN FROM MERCHANT_BROWSER_VIEW WHERE CUST_ID = 'A51B8CA2' ) AS RESULT WHERE RN BETWEEN 1 AND 20 TIA, Happy Holidays, jp __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Returning records from a function
I've spent the last few hours trying to come up with a function that at the most basic returns the result of a SELECT * FROM . This is easy to do in something like MSSQL or Oracle. For example in MSSQL i can do something like: CREATE PROCEDURE proc_name AS BEGIN SELECT * FROM sometable END How do I do something like that with a Postgresql function/stored procedure? All I really want to know is how to by using a function how can I return the results of a SELECT * query!? The end result of the query I need is more complicated, but I can't even get something that easy to work. Are functions unable to return result sets?? All of the examples i've seen always return a single value, like an int. That doesn't do me any good. I want to use a few stored procedures to create some faster processing for some queries that involve 3-4 tables plus require some input parameters, and return a result set. Any help would be appriciated. I've just recently started using PostgreSQL and I'm very impressed! I'm sure I'm jsut overlooking something easy, but for the life of me i just cant figure it out. Thanks, John ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] How to return records from a function
Hello everyone. I've spent the last few hours trying to come up with a function that at the most basic returns the result of a SELECT * FROM . This is easy to do in something like MSSQL or Oracle. For example in MSSQL i can do something like: CREATE PROCEDURE proc_name AS BEGIN SELECT * FROM sometable END How do I do something like that with a Postgresql function/stored procedure? All I really want to know is how to by using a function how can I return the results of a SELECT * query!? The end result of the query I need is more complicated, but I can't even get something that easy to work. Are functions unable to return result sets?? All of the examples i've seen always return a single value, like an int. That doesn't do me any good. I want to use a few stored procedures to create some faster processing for some queries that involve 3-4 tables plus require some input parameters, and return a result set. Any help would be appreciated. I've just recently started using PostgreSQL and I'm very impressed! I'm sure I'm jsut overlooking something easy, but for the life of me i just can't figure it out. Thanks, John ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to return records from a function
Thanks! That's somewhat of what i was looking for. However it seems that what I wanted to do is still not possible. I'm writing a Java application and wanted to use CallableStatement to call a PostgreSQL function. However it seems that the driver does not implement that functionality. I guess I can just use a regular statement calling a function but there seems to be problems doing that as well. Are there any other JDBC drivers for PostgreSQL? Thanks, john - Original Message - From: <[EMAIL PROTECTED]> To: "John Cavacas" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: February 2, 2003 10:52 AM Subject: Re: [SQL] How to return records from a function > Did you look at > http://techdocs.postgresql.org/guides/SetReturningFunctions > ? You need Postgresql 7.3 to do this. > Regards, > Tomasz Myrta ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] A simple way to Create type ...?
On Tuesday, September 16, 2003, at 05:27 PM, Christopher Browne wrote: What you want instead is CREATE DOMAIN. flexreg=# create domain addresstype varchar(50); The problem here is that you can't tell the difference between a addresstype column and a varchar(50) column in the row description information returned by SELECT. All columns just look like varchar(50). It would be nice if there was something as easy as CREATE DOMAIN but worked more like CREATE TYPE. Best, John DeSoi, Ph.D. ---(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] Complex Update
Problem: I have a table that has a field I want to plug with a value from other tables. I can do it with a perl program and a loop, but want to do it with a single SQL statement. Is there a way to do an update that combines a select statement containing a "where" that uses a field from the row to be updated? I would expect it to look something like this: update orderheader set total = (select sum(amount) from orderdetail where orderdetail.orderno=orderheader.orderno) ; Any ideas? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Object-relational features
On Mar 13, 2004, at 12:30 PM, Yasir Malik wrote:
For
example, using "create type as" is totally worthless because you can't
use
it as a field type in a table; you can't compose in another "create
type
as"; and you can't inherit another composite type. The only way to
create
a true type is to use "create type" and write C code as a shared
object,
so I'm basically doing everything C, which is not something I want to
do.
I'm not sure if this is what you are looking for, but it shows how to
create a column type based on the text type. So your selects will
return the column type as your custom type and you can process the
content accordingly. From reading the docs (and asking on the list) I
did not think this was possible either without writing external code in
C. But a post about something else finally provided the clues I needed
to get it working.
Best,
John DeSoi, Ph.D.
test=# create or replace function lispin(cstring, oid, int4) returns
lisp as 'varcharin' language 'internal' immutable strict;
NOTICE: type "lisp" is not yet defined
DETAIL: Creating a shell type definition.
CREATE FUNCTION
test=# create or replace function lispout(lisp) returns cstring as
'varcharout' language 'internal' immutable strict;
NOTICE: argument type lisp is only a shell
CREATE FUNCTION
test=# create type lisp (input=lispin, output=lispout,
internallength=variable);
CREATE TYPE
test=# create table tst (a lisp);
CREATE TABLE
test=# insert into tst (a) values ('1');
INSERT 18499 1
test=# insert into tst (a) values ('(+ 5 5)');
INSERT 18500 1
test=# select * from tst;
a
-
1
(+ 5 5)
(2 rows)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] sleep function
Is there a sleep function of some kind? I wanted to simulate a query taking a long time to execute for testing purposes. Thanks, John DeSoi, Ph.D. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] sleep function
On Aug 10, 2004, at 10:57 AM, Bruce Momjian wrote: I can't think of one, no. I think you will have to use one of the server-side languages and call a sleep in there. This is no good in the real world since it pounds the CPU, but it worked well enough for my testing purposes. Best, John DeSoi, Ph.D. create or replace function sleep (integer) returns time as ' declare seconds alias for $1; later time; thetime time; begin thetime := timeofday()::timestamp; later := thetime + (seconds::text || '' seconds'')::interval; loop if thetime >= later then exit; else thetime := timeofday()::timestamp; end if; end loop; return later; end; ' language plpgsql; ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] sleep function
Hi Greg, On Aug 22, 2004, at 11:52 PM, Greg Stark wrote: test=# create or replace function sleep(integer) returns integer as 'return sleep(shift)' language plperlu; CREATE FUNCTION The original request was for something in built-in to PostgreSQL. I have not used plperl (or any of the other pl languages), but I assume there will be additional installation and configuration issues to use them. But thanks for the example, it will be helpful if I need something more processor friendly. Best, John DeSoi, Ph.D. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] colored PL with emacs
On Aug 31, 2004, at 1:23 PM, Manuel Sugawara wrote: I have SQL highlighting, but what I want are colors for the PL/pgSQL key words. It would make PL programming much easier. Since the Pl/PgSQL code is quoted (x)emacs paints the whole thing using the string face. Delete one of the apostrophes delimiting the code while editing and you should get some highlighting. I'm working on an editor specifically designed for PostgreSQL and plpgsql programming. It colors syntax within function strings and helps get the quoting right (8.0 dollar quoting is also supported). The editor is based on emacs but it also has emulation modes that are more friendly for those who are not familiar with emacs. I hope to start beta testing the Mac OS X version next week, followed by a Windows version probably in October. I have not yet decided if I will do a Linux version, but it should be possible. The estimated price for this product is $65. Send me an email if you are interested in beta testing the Mac OS X version. Best, John DeSoi, Ph.D. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Postgres Doubt
On Sep 27, 2004, at 11:27 PM, sreejith s wrote: Hello, I am new to PostgreSQL. I have a doubt. Now i am doing one project in .NET technology with postgres(linux) as database. I am using pgADMIN and pgManager as tools for database releted activities. Now i am able to create functions with 'sql' language. When i select 'pgsql' as language for creating query, an error poping up. How to create Query using 'pgsql' language. if any new tools need to be installed. Plz rectify. Mail me back at [EMAIL PROTECTED] You need to use the createlang utility to add pgsql to your database. see http://www.postgresql.org/docs/current/interactive/app-createlang.html http://www.postgresql.org/docs/current/interactive/sql- createlanguage.html You generally want to do this for template1 so it will be available in any new database you create. Best, John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Hide schemas and tables
On Dec 13, 2004, at 5:25 AM, Markus Schaber wrote: Is there any way to hide schemas and relations a user does not have access privileges for? I suspect that the client (in this case, unavoidably excel via OLAP and ODBC) gets this information via querying meta tables, so there is no way to protect foreign schemas and relations from beeing seen. Yes, you are correct. I'm not aware of any way to hide this information. Best, John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Table History
On Dec 17, 2004, at 1:23 AM, Richard Sydney-Smith wrote: I expect this has been done MANY times and I wonder if a general purpose trigger exists or if not then can someone point me to an example set of triggers? I'm not aware of a "general purpose" trigger for this. If you just want some extra trigger examples other than what is in the documentation, there is a test file in the distribution with quite a few: src/test/regress/sql/plpgsql.sql Best, John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(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] Problems with Quotes
On Jan 12, 2005, at 1:08 PM, Kieran Ashley wrote:
I've tried using the replace() function to get rid of the ", but I
can't figure out how to use it without throwing an error. I tried
replace(col_name, '\"', '')
and several other permutations but to no avail, do I need to use
something like an ASCII character code in order to get rid of a quote?
If so which one, and if not, is there a better solution?
Try '"' as in
select replace('this "is" it', '"', '');
replace
this is it
(1 row)
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Problems with Quotes
On Jan 12, 2005, at 2:00 PM, Kieran Ashley wrote: I'm still not entirely sure _why_ that works, but it does... so I can go home now! ;) You should look at section 37.2.1 in the current docs. 8.0 has a new dollar quoting feature which makes this easier to deal with. http://www.postgresql.org/docs/7.4/static/plpgsql-development-tips.html John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Looking for examples of S/P
On Jan 19, 2005, at 4:03 AM, KÖPFERL Robert wrote: In order to learn SQL-Stored Procedure techniqes I'm looking for a series of examples. Where can I find examples of SQL and PL/pgSQL based stored procedures? Or any of you who wants to donate some? The pgEdit distribution includes an extensive example for importing and analyzing web server logs using only SQL and PL/pgSQL. Look in the pgEdit/Examples/web folder after installation. http://pgedit.com/download John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(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
Re: [SQL] editors with colum positioning for debugging?
On Jan 22, 2005, at 5:12 PM, Joel Fradkin wrote: My question is I don’t see an easy way to find an error. Typical error text is syntax error at or near "trans" at character 825 But determining character 825 is a little slow by hand. In pgEdit you can use the "Goto Point" command to jump to a specific offset in the file. I hope to have something better in the near future to automatically move the cursor for you when an error is detected. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(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] Moving from Transact SQL to PL/pgsql
On Jan 23, 2005, at 10:22 PM, Duffy House wrote: The first issue will be getting up to speed on PL/pgsql. Where can I find primer on PL/pgsql, with lots of examples? How silimar is PL/pgsql to PL/SQL under Oracle? The PostgreSQL documentation is the place to start: http://www.postgresql.org/docs/8.0/interactive/plpgsql.html The PostgreSQL distribution has a file with some examples in it: src/test/regress/sql/plpgsql.sql The pgEdit distribution (http://pgedit.com/download) has a fairly extensive plpgsql example for importing and analyzing web server logs. I'm not familiar with Oracle, but there is a porting section in the documentation: http://www.postgresql.org/docs/8.0/interactive/plpgsql-porting.html John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(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] plpgsql select into with multiple target variables
The docs say: The result of a SELECT command yielding multiple columns (but only one row) can be assigned to a record variable, row-type variable, or list of scalar variables. This is done by: SELECT INTO target select_expressions FROM ...; where target can be a record variable, a row variable, or a comma-separated list of simple variables and record/row fields. The select_expressions and the remainder of the command are the same as in regular SQL. So, I'm trying to do the "list of scalar variables" target, but I can't get the syntax right. Something like SELECT into varx, vary, varz, colx, coly, colz, FROM I've tried parens and various other things but no luck. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] error in function!!
On Jan 31, 2005, at 1:59 PM, Ing. Jhon Carrillo wrote: ERROR: function tschema.sp_actualizar_contacto(integer, "unknown", "unknown", "unknown", "unknown", "unknown", "unknown", integer, "unknown", "unknown", "unknown", "unknown", "unknown", "unknown", "unknown", "unknown", "unknown", "unknown", "unknown", integer, integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. I need to know if the "insert sentence" was sucesfull, how do i do? This message is telling you that your function call using tschema.sp_actualizar_contacto was not correct. You must have all parameters in the function call and they must all be the correct type. So the message above says the first parameter passed was an integer, but your function expects the first parameter to be text. Note that you can remove all of the ALIAS declarations and use parameter name directly. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Updating selected record
On Feb 8, 2005, at 9:05 AM, Levente Lajko wrote: I have problem with a table from where I select specific field data SELECT filed1, field2 FROM tbl WHERE progress = 1 LIMIT 1 through a perl script or multiple instances of that script. The script sets the progress value of the progressed record to 0, so that other processes not to access that row. I presume I would need some locking solution, only I haven’t found the real one. I hope somebody has a bright idea. Maybe you are looking for SELECT FOR UPDATE as in SELECT filed1, field2 FROM tbl WHERE progress = 1 LIMIT 1 FOR UPDATE; This locks the row for the remainder of the transaction. See http://www.postgresql.org/docs/8.0/interactive/sql-select.html#SQL-FOR- UPDATE John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How to iterate through arrays?
On Feb 9, 2005, at 9:26 AM, NosyMan wrote: I think the problem is not there. I got the following error: 'ERROR: syntax error at or near "[" at character 1234', the line is: RAISE NOTICE '% ...',update_query_params[1]; The problem here is RAISE, NOTICE, etc. can only use simple variables in the format string. Try it like: myTextVar := update_query_params[1]; NOTICE '% ...',myTextVar; John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] RE: [SQL] trrouble inserting stuff like é
On Feb 18, 2005, at 11:15 AM, Joel Fradkin wrote: How do I tell the connection to use Unicode? Try SET client_encoding TO 'UNICODE'; http://www.postgresql.org/docs/8.0/interactive/sql-set.html But it should default to the database encoding, so I'm not sure if that is the problem. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] [ADMIN] Postgres schema comparison.
On Mar 7, 2005, at 4:33 AM, Stef wrote: I have the wonderful job of re-synch'ing all the schemas out there not conforming to the master. I've looked everywhere for something that will help doing this. I'm specifically looking for a way to do a sumcheck or something similar on tables and/or schema as a whole to be able to do a table comparison with the master database. Develop a function that builds a string describing the tables/schemas you want to compare. Then have your function return the md5 sum of the string as the result. This will give you a 32 character value you can use to determine if there is a mismatch. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [ADMIN] Postgres schema comparison.
On Mar 7, 2005, at 10:09 AM, Stef wrote: Is it possible to somehow pass the output of : "\d [TABLE NAME]" to this function? If not, what would return me consistent text that will describe the columns, indexes and primary keys of a table? I'm not sure you can use \d directly, but if you startup psql with the -E option it will show you all the SQL it is using to run the \d command. It should be fairly easy to get the strings you need from the results of running a similar query. The psql source is a good place to look also. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] SQL query help?
SELECT Count(*) FROM Work WHERE (UserID='user1' AND MAX(Finished)=0) Work:- +---+---++-+--+ | ID | JobID | UserID | Finished | Comment | +---+---++-+--+ | 1 | 1| user1 | 0 | ...| | 2 | 1| user1 | 1 | ...| | 3 | 2| user2 | 0 | ...| | 4 | 3| user1 | 0 | ...| | 5 | 2| user2 | 0 | ...| | 6 | 2| user1 | 1 | ...| | 7 | 3| user1 | 0 | ...| +---+---++-+--+ All I want it to do is return the number of unfinished jobs for a specific user. In this example it would return 1 because job number 3 is not finished and user1 was the last person working on it. but I keep getting MySQL error # - Invalid use of group function ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] pl/pgsql problem with return types
On Mar 11, 2005, at 5:54 AM, Juris Zeltins wrote: FOR P IN select pageid from pages This way you are only getting the pageid column. I think what you want is FOR P in select * from pages so that P contains the complete pages record. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Question on triggers and plpgsql
On Apr 7, 2005, at 5:45 PM, Carlos Moreno wrote: The thing seems to work -- I had to go in a shell as user postgres and execute the command: $ createlang -d dbname plpgsql (I'm not sure I understand why that is necessary, or what implications -- positive or negative -- it may have) As a security measure, no pl language is available by default. What you did is correct. There is not much (any?) risk with pl/pgsql, so you can install it in template1 so it will be available in any new database you create. Am I doing the right thing? Have I introduced some sort of catastrophe waiting to happen? I did not notice any problems. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Question on triggers and plpgsql
On Apr 8, 2005, at 9:41 AM, Sean Davis wrote: Just one detail, but in the form of a question. In the original posting, I think the trigger was doing the logging for something happening on a table as a before insert or update--I may be wrong on that detail. I would think of doing such actions AFTER the update/insert. In the world of transaction-safe operations, is there ANY danger in doing the logging as a BEFORE trigger rather than an AFTER trigger? Good point. I think both will work in this case and it would depend on the application if it makes a difference. You definitely want an AFTER trigger if you need to see the final state of the row before making changes. In this case the assignment of the column does not depend on any other factors so it would not seem to matter. But I agree from a semantics point of view, an AFTER trigger might be a little better for this. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Question on triggers and plpgsql
Tom, Thanks for setting the record straight. It has been a while since I have written a trigger and I forgot that you can't modify the row in the AFTER trigger. Makes perfect sense. For the record, here is what the docs say: Typically, row before triggers are used for checking or modifying the data that will be inserted or updated. For example, a before trigger might be used to insert the current time into a timestamp column, or to check that two elements of the row are consistent. Row after triggers are most sensibly used to propagate the updates to other tables, or make consistency checks against other tables. The reason for this division of labor is that an after trigger can be certain it is seeing the final value of the row, while a before trigger cannot; there might be other before triggers firing after it. If you have no specific reason to make a trigger before or after, the before case is more efficient, since the information about the operation doesn't have to be saved until end of statement. It might be worth adding a sentence here that explicitly states modifications can only be made in the BEFORE trigger. I did not see that anywhere else in the document. On Apr 8, 2005, at 10:36 AM, Tom Lane wrote: No, actually Carlos wanted to do new.last_modified = now(); so he *must* use a BEFORE trigger --- AFTER is too late to change the data that will be stored. Generalizing freely, I've seen three basic uses for triggers: 1. Modify the data that will be stored. 2. Check that data is valid (eg, consistent with another table). 3. Propagate updates in one place to other places. Clearly #1 must be done in BEFORE triggers. #2 and #3 could be done either way. They are often done in AFTER triggers because that way you *know* that any case-1 triggers have done their work and you are looking at the correct final state of the row. But you could do them in a BEFORE trigger if you were willing to assume that no later-fired trigger would make a change that invalidates your check or propagation. AFTER triggers are relatively expensive (since the triggering event state has to be saved and then recalled) so I could see making that tradeoff if performance is critical. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] getting count for a specific querry
On Apr 8, 2005, at 3:37 PM, Joel Fradkin wrote: I don't think my clients would like me to aprox as it is a count of their records. What I plan on doing assuming I can get all my other problems fixed (as mentioned I am going to try and get paid help to see if I goofed it up some where) is make the count a button, so they don't wait everytime, but can choose to wait if need be, maybe I can store the last count with a count on day for the generic search it defaults to, and just have them do a count on demand if they have a specific query. Our screens have several criteria fields in each application. Here is an interface idea I'm working on for displaying query results in PostgreSQL. Maybe it will work for you if your connection method does not prevent you from using cursors. I create a cursor an then fetch the first 1000 rows. The status display has 4 paging buttons, something like this: |< < rows 1 - 1000 of ? > >| The user can hit the "next" button to get the next 1000. If less than 1000 are fetched the ? is replaced with the actual count. They can press the "last" button to move to the end of the cursor and get the actual count if they need it. So here the initial query should be fast, the user can get the count if they need it, and you don't have to re-query using limit and offset. The problem I'm looking into now (which I just posted on the general list) is I don't see a way to get the table and column information from a cursor. If I fetch from a cursor, the table OID and column number values are 0 in the row description. If I execute the same query directly without a cursor, the row description has the correct values for table OID and column number. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] getting count for a specific querry
On Apr 9, 2005, at 11:43 AM, Bob Henkel wrote: Forms also offers a button that say get hit count. So if you really need to know the record count you can get it without moving off the current record. That's a good idea too. Maybe in my interface you could click on the ? to get the count without changing the rows you are viewing. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(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] php wrapper
On Apr 21, 2005, at 8:00 AM, Mauro Bertoli wrote: Hi, I need a PHP wrapper for PostgreSQL... I found 1000 small "+/- identicals" wrappers but incompleted There's an "ufficial" or an suggested PHP wrapper? Why not use the built-in PHP functions for PostgreSQL? If by wrapper you mean an abstraction to support other databases, what other systems do you need to support? PEAR seems to have a nice databases abstraction layer (DB.php). Drupal uses this to support PostgreSQL and MySQL. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How do I quit in the middle of a SQL script?
On May 20, 2005, at 1:22 PM, Wei Weng wrote: Say if I want to add a small snip of code in front of the sql script generated by the pg_dump, to check for something then if the condition doesn't match, the script terminates right away. (Without actually doing the restoring stuff that the following large chunk is supposed to do) Can I do that? Put this at the start of the file to make psql stop if there is an error: \set ON_ERROR_STOP 1 And is it a good idea to add arbitrary code to the database dump sql script? No problem if you know what you are doing and/or have good backups :) John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Error on dynamic code.
On Jul 14, 2005, at 3:46 AM, Mark J Camilleri wrote: The funny thing is that the documentation I read about SELECT INTO and RECORD types give the following example, amongst others: See the section below that on EXECUTE: The results from SELECT commands are discarded by EXECUTE, and SELECT INTO is not currently supported within EXECUTE. So there is no way to extract a result from a dynamically-created SELECT using the plain EXECUTE command. There are two other ways to do it, however: one is to use the FOR-IN-EXECUTE loop form described in Section 35.7.4, and the other is to use a cursor with OPEN-FOR- EXECUTE, as described in Section 35.8.2. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] funstions for parsing words
I have a table called Phrases that holds the text of a phrase. I want write a query that will return all the words found in all the text of the Phrases. Like so: Phrases: "Hello World" "Goodbye World" "I like candy Words (select statement result): "Hello" "World" "Goodbye" "I" "Like" "Candy" Is anything like this possible? Thanks alot. Your Friend, John Kopanas ---(end of broadcast)--- TIP 1: 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] echo/printf function in plpgsql
On Jul 19, 2005, at 11:58 AM, Andreas Joseph Krogh wrote: I see. Can I make the ouput somehow less verbose? It spits out a lot of noise for each "NOTICE": If you just want to output some information to the log, you can use something like this: raise log 't is %', t; If I recall correctly, the values to be inserted into the format string can only be variables, not expressions. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] incorrect syntax for 'plpgsql' function to test boolean values
On Aug 6, 2005, at 10:52 PM, Ferindo Middleton Jr wrote: ERROR: operator does not exist: boolean == boolean HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. CONTEXT: SQL statement "SELECT (( $1 == true) && ( $2 == true))" PL/pgSQL function "trigger_insert_update_registration_and_attendance" line 13 at if What is wrong with my syntax above? Too much C programming :). You just want a single equal sign. select true = true; ?column? -- t (1 row) John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Number of rows in a cursor ?
On Aug 24, 2005, at 6:31 AM, Bo Lorentsen wrote: How sad, then I have to repeat the query, first for counting and last for data fetch :-( No, you can use the MOVE command and read how many rows you moved with something like MOVE LAST IN mycursor; http://www.postgresql.org/docs/8.0/interactive/sql-move.html John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Need help with 'unique parents' constraint
Greg Sabino Mullane writes: > Not just old-fashioned, [having only one mother is] the biological law! I see you aren't up on current research. -- John Hasler [EMAIL PROTECTED] Elmwood, WI USA ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
