hello everyone
i am having problem with multidimensional arrays in plpgsql following
is the source code of the function which i am trying to run
CREATE OR REPLACE FUNCTION test() RETURNS VOID AS $$
DECLARE
x INTEGER[10][10];
tmp VARCHAR(40);
BEGIN
x[3][1] := '20'; --i have even
"Joel Fradkin" <[EMAIL PROTECTED]> writes:
> I also tried a simple select * from tblcase where clientum = 'SAKS'
Try:
explain analyze select * from tblcase where clientum = 'SAKS'
Send the output.
--
greg
---(end of broadcast)---
TIP 4: Don't
Joel Fradkin wrote:
I also tried a simple
select * from tblcase where clientum =
‘SAKS’
On both MSSQL and
Postgres.
MSSQL was 3 secs,
Postgres was 27 secs.
There is a key for
clientnum, but it appeared on both
systems (identical Dell Desktops Postgres is running Linux M
Basically the question was why would a view use an indexed search on one
result set but a seq search on a larger result set. Same view only
difference is how many rows are returned. The large result set was doing a
seq search and did not return after several minutes. The same sql ran in 135
seconds
I also tried a simple select * from tblcase where clientum =
‘SAKS’
On both MSSQL and Postgres.
MSSQL was 3 secs, Postgres was 27 secs.
There is a key for clientnum, but it appeared on both
systems (identical Dell Desktops Postgres is running Linux MSSQL is XP) it did
not do a indexed
updating 40.000 records should take no longer than a couple of minutes.
I think you should optimise your query before going any further.
You have an inner SELECT sentence that executes before anything. It
joins EVERY row in your table (1,000,000+) with at most 3 other rows in
the same table, so
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
Have you looked at the documentation on triggers?
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Jan 23, 2005, at 11:23 AM, Klaus W. wrote:
Hi!
Because i got no answer in pg
Kevin Duffy wrote:
Hello:
I
am starting a project using Postgres. The requirements are very
similar to work I have done in the past using M$-SQL. Therefore, there
are many Transact SQL stored procedures I need to port over to PL/pgSQL.
Where
would I find documentation on
On Tue, 25 Jan 2005, Joel Fradkin wrote:
Yes I am using datepart, but not seeing how with a datediff.
Maybe I just need to do the date math and put a date part on the result.
I will play around a bit when I hit one not looking for days.
http://www.pgsql.ru/db/pgsearch/index.html?q=datediff
Joel Fra
On Tue, Jan 25, 2005 at 04:09:09AM +1300, Steve Castellotti wrote:
> Surely there's a simple way I can trace REFERENCES in a particular
> column across tables?
The pg_constraint table contains foreign key constraints. Here's
an example query that appears to work in trivial tests:
SELECT c.c
Hi!
Because i got no answer in pgsql.novice, i'll try it here. But maybe it
was even too easy for .novice?
Lets say there are three tables:
CREATE TABLE c (id SERIAL, data VARCHAR(20));
CREATE TABLE b (id SERIAL, c_id INTEGER REFERENCES c, data VARCHAR(20));
CREATE TABLE a (id SERIAL, b_id INTEG
On Mon, Jan 24, 2005 at 12:14:22PM -0500, Kevin Duffy wrote:
> Where would I find documentation on PL/pgSQL, with examples?
On the PostgreSQL web site, or perhaps on your own server if you've
installed the documentation.
http://www.postgresql.org/
Follow the "Documentation" link, then follow t
Hi Gary,
I went through a conversion from Oracle to PostgreSQL about 9 months ago.
There's a little bit of documentation in the PostgreSQL manual
http://www.postgresql.org/docs/7.4/interactive/plpgsql-porting.html
or
http://www.postgresql.org/docs/8.0/interactiveplpgsql-porting.html
I had ment to s
Hello all. I'm trying to write a recursive procedure to allow me to handle some data backup routines and deal with conflicts when restoring from within the program.
Essentially, what I'd like to be able to do is if a table called "image" has a column called "file_id" which references
Alex Turner <[EMAIL PROTECTED]> writes:
> I am also very interesting in this very question.. Is there any way to
> declare a persistant cursor that remains open between pg sessions?
> This would be better than a temp table because you would not have to
> do the initial select and insert into a f
Thanks a lot.. That is what i searched.. In fact your query is very good for
little changes, but i will have to use another method when updating all my rows
because the performance is not very good alas.
My data set contains something like 4 rows to update in 1+ million records
and data_raw
Gary Broadbent wrote:
Hi,
I have a database in Oracle that I want to convert to PostgreSQL.
I have exported the scripts used to create the tables, constraints and
sequences in Oracle and wish to convert these to postgreSQL scripts now.
Is there an easy way to do this?
These aren't too hard (stor
Hello:
I am implementing a new system using Postgres. Well most of the system will
be new,
but some parts will be very similar to a system currently running under
M$-SQL.
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 s
Hello:
I am starting a
project using Postgres. The requirements are very similar to work I have
done in the past using M$-SQL. Therefore, there are many Transact SQL
stored procedures I need to port over to PL/pgSQL.
Where would I find
documentation on PL/pgSQL, with examples?
How c
Isn't this a prime example of when to use a servlet or something similar
in function? It will create the cursor, maintain it, and fetch against
it for a particular page.
Greg
-Original Message-
From: Richard Huxton [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 20, 2005 10:21 AM
To:
On Thu, 2005-01-20 at 19:12 +, Ragnar Hafstað wrote:
> On Thu, 2005-01-20 at 11:59 -0500, Greg Stark wrote:
>
> > The best way to do pages for is not to use offset or cursors but to use an
> > index. This only works if you can enumerate all the sort orders the
> > application might be using an
PFC wrote:
> Return only four rows beginning at second row:
> >
> > SELECT count(*) AS count, name, year FROM a
> > GROUP BY name, year
> > ORDER BY count DESC, name ASC
> > LIMIT 4 OFFSET 1;
> >
> > count name year
> > --- -- --
> >3 joe2004 s,e,e
> >2
Yes I am using datepart, but not seeing how with a datediff.
Maybe I just need to do the date math and put a date part on the result.
I will play around a bit when I hit one not looking for days.
Joel Fradkin
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel. 941-753-7111 ext 305
[E
Hi there:
Thank you for the response, which gave me what I wanted.
Here is a follow-up question..
First a recap:
Table a contains the names of individuals, the places
they have visited and the year in which they were visited.
Let's see who has visited where and when:
SELECT * FROM a;
name
On Thu, 2005-01-20 at 11:59 -0500, Greg Stark wrote:
> The best way to do pages for is not to use offset or cursors but to use an
> index. This only works if you can enumerate all the sort orders the
> application might be using and can have an index on each of them.
>
> To do this the query woul
I am also very interesting in this very question.. Is there any way to
declare a persistant cursor that remains open between pg sessions?
This would be better than a temp table because you would not have to
do the initial select and insert into a fresh table and incur those IO
costs, which are oft
Hi,
I have a database in Oracle that I want to convert to PostgreSQL.
I have exported the scripts used to create the tables, constraints and
sequences in Oracle and wish to convert these to postgreSQL scripts now.
Is there an easy way to do this?
Regards,
Gary.
___
On Mon, Jan 24, 2005 at 16:34:09 -,
Martin Schäfer <[EMAIL PROTECTED]> wrote:
>
> I'm using the PostGIS spatial extension. Some of my spatial queries (like
> live zooming and panning) should only be performed when the column
> containing the spatial data is spatially indexed, otherwise the
Hi,
i have the following SQL-Problem:
We are using 2 tables. The first, called plan, is holding planned
working times for employees per
tour:
plan.id_tour
plan.id_employee
plan.begin_time
plan.end_time
The second table 'work' stores the actual worked times for employees per tour:
work.id_tour
O Joel Fradkin έγραψε στις Jan 25, 2005 :
> Hi all working my way through our views and all is going very well.
>
> We use datediff in MSSQL a bit and I read about the field1::date -
> field2::date to return the days numerically.
>
> Is there any way to get months and years besides guessing days
Hi all working my way through our views and all is going
very well.
We use datediff in MSSQL a bit and I read about the
field1::date – field2::date to return the days numerically.
Is there any way to get months and years besides guessing
days / 30 for months etc?
Joel Fradkin
32 matches
Mail list logo
