END LOOP
END;
than:
SELECT temptable
-Josh
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small bus
D'Arcy,
> You use the chkpass type that I just commited to the distribution
> (finally.)
> Here is an example of its usage.
Does that mean that chkpass is part of 7.1, or only the current CVS?
-Josh
__
).
Speaking of which, why doesn't PostgreSQL allow the use of "+" between
two VARCHARs to mean the same as "||" ? Is there an addition operation
for PgSQL for VARCHAR other than concatination?
-Josh
> > I need to know if there is a sql function implemented in postgres
.
RIght now, I'm copying the file to a Win32 machine and using MS Word
for the search-and-replace, but I'm sure there's got to be a better way
... *without* learning VI or Emacs. Help?
-Josh
__AGLIO DATAB
estore. Could it be as simple as the dump file trying to create the
view before the function? Or plpgsql not being defined as a language on
the target server?
BTW, there is a better-performance way to do the same thing you're doing
with that function. Please browse the list archives with
the line. If e-mail addresses are that important to your application, I
greatly encourage you to split the field.
-Josh Berkus
P.S. Roberto, please add the above to our function library.
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Co
t; 1), 60) AS CHAR(60));
> RETURN mail_box || mail_domain;
I meant:
RETURN mail_domain || mail_box;
> END;'
> LANGUAGE 'plpgsql';
>
> Then:
>
> SELECT user_id, email, email_sort(email) as sortcol
> FROM users
> ORDER BY sortcol;
>
-Josh
r that will
allow you to backfill exisiting records.
2. Add a column of type INT4 to each table ('gsq')
3. Create the default value of this column as NEXTVAL('gui_sq').
4. Backfill existing records.
5. Add a unique index on each table for this column.
You
)
c. Return the s_key from the function.
5. Query from you front-end app on sv_results JOIN searches where
search_sq = s_key.
This works quite well, especially for web applications.
-Josh
__AGLIO DATABASE SOLUTIONS___
J
interval, and while you can add an interval to a date you cannot add
two dates.
Additionally, if you browse to Roberto Mello's PG/plSQL function library
(see link for the PostgreSQL.org web site) you will find an extension to
the OVERLAPS function that I find quite useful (I should, I wrote it!
how do I
> get the
> returned record in PL/pgSql
No, it is not possible. I know the list archives are kinda hard to
search, but we've discussed this question ad naseum; in fact, I just
provided a workaround earlier this week.
-Josh
__AGLIO DATABASE SOLUTIONS______
pts to use the function after PGAccess
editing are met with: 'Parse error at or near ""'
Once the *identical* function text is reloaded via PSQL command-line,
however, the function works.
I'm using Postgres 7.1 RC2 and PGAccess 0.98.5.
ly, what I want is:
SELECT ALL Wednesdays BETWEEN 5/1/01 AND 6/1/01;
Which results in:
Wednesdays
---
5/2/01
5/9/01
5/16/01
5/23/01
5/30/01
Thanks for any suggestions!
-Josh Berkus
__AGLIO DATABASE SOLUTIONS_
em:
1. Test fn_save_order: it's working.
2. Open fn_save_order in PGAccess.
3. Add '--test comment' on its own line.
4. Save fn_save_order.
5. test it: "Parse Error at or near "" "
Unfortunately, I can't afford to pay Constatin for d
mn x.
This requires me to build a list of all possible Wednesdays (within a
date range), preferably *without* first having a table of all dates in
existance! Some sort of manipulation of the date processor should be
possible, shouldn't it?
-Josh Berkus
__AGLIO DATABASE SOLUTIONS_
s isn't made easier by the fact that functions currently
> can't
> return sets without extreme wizardry.
Thanks for the feedback ... it's good to know at least when something is
impossible.
Looks like I'll have to build a nightly table of all Wednesdays in the
las
Folks,
I just subtracted two dates and got an INT4, rather than the INTERVAL I
was expecting. What goes on here?
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED
significantly.
This makes some sort of sense, now.
Correct me if I'm wrong:
DATE + INT4 = DATE
DATE - DATE = INT4
But:
DATETIME + INTERVAL = DATETIME
DATETIME - DATETIME = INTERVAL
-Josh
__AGLIO DATABASE SOLUTIONS___
ERT INTO timecard_due_dates ( assignment_usq, week_ending )
SELECT assignments.usq, check_date
FROM assignments
WHERE (status > 2 OR (status < 0 AND status > -81))
AND start_date <= check_date
AND end_da
r) + '.' + RTRIM(x.LANG)) t = y.SRC_ID
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small b
ery issue. The answer, in short, is that you
can't do it in SQL. Your choices are to build the reference table, or
to use and external procedural language (such as Perl).
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
s error would be. The
last thing I want for my users is to have to contend with "5301: No
rights on TABLE client_contacts."
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology
ng SQL DB security, this can be a
serious headache, as opposed to a single update with an interface-based
system.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data
odes within their context without tying up
those codes for all time or preventing your users from changing the
codes.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and
meday in the future, but then again it may not
(it's a design philosophy issue, I believe).
I don't know of *any* RDBMS that allows foriegn keys between databases!
If referential integrity is your goal, why on earth is the related data
in two different databases?
-Josh Berkus
t worth it to me to hunt down the
issue.
-Josh
> FYI, I could not reproduce this problem in the current CVS sources.
>
> I created a function 'select 1;' and saved it, then opened the
> function
> and added a comment line, saved that, and it worked fine.
>
> >
were restored but many were not. No errors were logged.
Can someone give me some help? The application is due next week, and I
can't afford this setback. Grazie.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Com
files -- one for DDL, one for data -- that it
fails.
BTW, I'm using 7.1RC2
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 5
were restored but many were not. No errors were logged.
Can someone give me some help? The application is due next week, and I
can't afford this setback. Grazie.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Com
.SQL for Smarties
> Also, would you be able to tell me how to use
> a
> nested DQL quesry/join query with an ADO command object, i.e the
> commandtext
> property? Thank you for your help.
This is the wrong list for questions about ADO. Try pgsql-interfaces
instead, or perhap
Peter,
> FUNC_MAX_ARGS in config.h; see the comments there.
Darn! I asked this a year ago, and was told by somebody at Hub.org that
it couldn't be changed ...
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Design by F. Pascal) to include date
ranges;
4) You can even add a "leave time rule history" table to keep track of
how leave time is calculated over the history of the company (e.g. what
if leave time was 14 days per year through 1999, but decreased to 10
days per year in 2000?)
5) Any
run on a (daily/weekly/monthly) basis.
3. Create a cron item that calls the shell script (1.).
Handling errors etc. obviously adds a layer of complexity to the
scripting. I also have the functions write to a schedule_log table
showing what they've done.
-Josh
__AGLIO DATABASE SOLUTION
ng the dates in only one format, and b) be really complicated
for leap years and the millenium.
I am hoping that someone has already dealt with this issue in a way
that date validation can be trappable.
Thanks for your advice.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
t and address for each company.
Of course, this all requires some sophistication in the user interface,
as one has to make the user distinguish between address corrections
(UPDATE) and new addresses (INSERT) which possibly replace old addresses
(UPDATE SET STATUS = old).
-Josh Berkus
_
Richard,
> Out of curiosity Josh, why aren't you validating in PHP? - only takes
> a
> couple of lines there.
We are. It's just that all other validation takes place inside the
PL/pgSQL functions in order to keep all the business logic in the
database. Having one form o
of things, but no change.
The first thing you should do is modify your program so that it outputs
the query text it's actually passing to the database to a text file or
stdout instead. Once you see the actual query and not a bunch of
string variables, the error ma
---
> 2001-01-01 BC | 0001-01-01 BC
> (1 row)
Quick! Call the presses! Stop the computers!
I could also make a number of Christian theological jokes, but I don't
want to offend anyone on the list
-Josh
__AGLIO DATABASE SOLUTIONS___
Tom, Stephan,
Hey! I thought you couldn't do ORDER BY in views ... yet I just did.
Is this a new thing, or am I just getting my Trasact-SQL and my
PostgreSQL mixed up again?
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh B
LECT * FROM test_view ORDER BY city;
Does the second ORDER BY override or suppliment the view ORDER BY, or is
it ignored?
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTE
to code in an exception to
the VIEW optimizer that doesn't push down WHERE clauses if the VIEW has
an ORDER BY ... LIMIT statement. Sure you wanna get into this?
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete in
elds from the query, it gives me the <7s response time I'd
expected.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTE
wice, then it doesn't matter which one
you group by.
Third, Matt, PostgreSQL 7.1.2 DOES support aliasing in GROUP BY
clauses. Maybe it's time to switch databases?
select client_name as clname1, client_name as clname2, AVG(status)
from clients
group by clname1, clnam
ns.
You'll need to get advice from other list members or the docs (and don't
forget the "non-FAQ Documentation" page!) on how to use these other
languages, as I have had no need for them, to date.
-Josh
__AGLIO DATABASE SOLUTIONS___
l/pgsql part is
> just a
> small portion of the whole thing. I still need to figure out a way to
> export
> data which I calculate to let gnuplot create pretty pictures for me.
>
> And today is the first day, I try pl/pgsql ... Today, the data import
> program mus
ve no time to look at it myself;
> any volunteers out there?
While you're (whoever) at it, OFFSET won't take expressions either.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology
Richard,
> > Josh - if I try and do OFFSET at the same time (presumably it's the
> same
> > change) do you fancy acting as a sanity test site?
>
> What am I talking about - OFFSET is going to be parsed by the SQL
> parser not
> the plpgsql parser.
Not so, n
old increase in JOIN performance by
switching to INT8 keys. In practice, its probably more like double or
triple but that ain't bad, either.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information
re-arrange the rows
according to some external criteria.
If it were me, I'd do it through interface (or better) middleware code,
disabling the user's ability to insert or delete rows directly and
forcing them to push inserts and deletes through some kind of function,
whether PL/pgSQL or Java-
l') cp
ON members.mid = cp.mid
ORDER BY members.name;
You're experiencing the usual problem encountered by procedural
programmers when they first start on SQL. SQL is a declarative
language, and requires a different knid of thinking than procedural
languages. Thus the use
data in your query,
you can insure that all records are displayed.
Frankly, I'm surprise that you're not getting errors when you try to
DELETE a record. This usually happens in tables without a unique key.
It's been my argument for some time that PostgreSQL should require a
pr
is designed to do. Plus my way and Tom's way
can be built as views, allowing further optimization. Not to mention
that pure SQL will always be faster than even a trusted language.
In other words, my and Tom's queries should run about 50 times as fast
as the procedural method for large
Dado,
Maybe we're looking at this the hard way. Have you thought of simply
putting in a DATETIME column and sorting by that?
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [
Bruce, Chris,
A lot of us would like a fuller PL/SQL implementation in PL/pgSQL.
However, Jan is busy with other things and I don't see anyone stepping
up to the plate to take on the project.
-Josh
__AGLIO DATABASE SOLUTIONS__
N
> >>seq_name := ''proj_pts_'' || proj_ID;
> >>create sequence seq_name;
> >>END;
> >>RETURNS seq_name;'
This should read: RETURN seq_name
No "S".
> >>LANGUAGE 'plpgsql';
-Jos
eSQL require a primary key at
table creation. Frankly, I'm a little mystified as to why this was not
done already, but it's not too late to correct ...
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete informa
learned the hard way.
For a more exhaustive explanation of the necessity of uniqueness and
primary keys, please pick up a copy of Fabian Pascal's "Practical Issues
in Database Design."
-Josh
__AGLIO DATABASE SOLUTIONS___
Jeff, Karl,
Actually, he's confusing 3 functions. See postgresql.org --> user
lounge --> interactive docs --> functions and operators --> string
functions.
-Josh
> You are confusing the syntax of two similar functions:
> position ('substring' in 'string&#
tals". If you
would prefer another language, poll the list -- someone may be familiar
with other texts.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
an
ntical column and
variable names will confuse the compiler and result in errors.
It'd have been nice if PL/pgSQL supported variable naming with a special
character preifx (e.g. $variable). Does anyone know of a character that
won't give the parser fits?
of direct SQL command access.
I took the second approach to solve a similar problem, because I had
quite a number of other business rules I needed to apply, and adding the
special relationship rule was only one more.
-Josh
__AGLIO DATABASE SOLUTIONS___
n there are.
This is good because it now means I can use the logical syntax of
SELECT data INTO var ...
which to me is easier to read, for everything.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete informat
just need to group the min(datetime) by it.
The answer to this question is simple:
SELECT min(datetime) as mintime FROM logs GROUP BY sid;
However, I get the impression that your intended problem was more
complicated. Can you re-explain it?
-Josh
__AGLIO DATABASE SOLUTIONS
Guard,
> > select nullif(NULL,5)
>
> ++
> | case |
> ++
> | |
> ++
>
Er... what were you expecting, exactly?
Except for IS NULL (and COALESCE, which uses IS NULL) any operation
involving a NULL is also NULL.
-
it selects the first
one alphabetically so that you don't get two files for one SID.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 5
tely unsuitable for column comparisons on large tables.
Can anyone suggest some shortcuts here? Perhaps using pl/perl or
something similar?
Grazie!
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information te
but metaphone is not so
useful for non-english versions of postgres.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for
grams existed
outside fo expensive proprietary software.
Now, who can I talk into porting them (metaphone, levenstein) to
Postgres? Hey, GreatBridge folks? (this would be a significant value
enhancement for Postgres)
-Josh
__AGLIO DATABASE SOLUTIONS___
n't help with the porting of metaphone or levenstein (but will
benefit immensely), I pledge to write a name-alike data checking
PL/pgSQL function which I will post to Roberto's library for public
consumption.
-Josh
__AGLIO DATABASE SOLUTIONS___
but by then
> hopefully we'll have changed things...)
Yeah, sure. That's what my father said in 1964 when they talked about
the potential problems with 2-digit dates on the UNIVAC II ...
;-)
-Josh
__AGLIO DATABASE SOLUTIONS___
for you to work with; DATE/INTEGER is easier
for math, and DATETIME/INTERVAL is easier for calendar comparisons.
See the docs on data types, functions, and operators at PostgreSQL.org.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
AQ, as it seems
inconsistent behavior.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businesses
erver is so far off the ANSI standard for Dates & Times that no
application may be ported from another server which relies on dates and
time manipulation.
FYI.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Comp
ry. :( I'm stricly a "high-level user".
I can, however, document it so that others won't pester you for
questions about why it doesn't work.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complet
s 3 months'::INTERVAL / '1 week'::INTERVAL (and we
don't care about the fractional week left over).
Thus we don't want to hold up simple and obvious date multiplication and
division just to deal with the wierdo cases.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS
ired) / '2
weeks'::INTERVAL
I don't want to go through a bunch of non-ANSI SQL-compliant conversion
functions to do it. Especially not as this is just what the ANSI SQL
data type and operator specs are designed to support.
-Josh
______AGLIO DATABASE SOLUTIONS__
o I propose that we suppot the first two and disallow the third.
Thus I think that we can adhere to the spec, while still providing the
functionality developers want and avoiding a whole lot of '5 months 11
minutes' type headaches.
-Josh
__AGLIO DATABASE SOLUTIONS
to:
to_weeks(current_timestamp - hire_date) / '2 weeks'
... otherwise division and multiplication operators for time values
don't do us much good, as we'd be forced to integer-ize all intervals
before we can perform any operations on them at all
their local
time and date according to local rules (admins of worldwide web servers
exempted).
So ... my first question: surely someone has grappled with this problem
already? Surely some professor of C.S. in a university somewhere has an
answer for us?
-Josh
P.S. My head hurts, now.
__A
,
> but have
> lots of trouble makeing it put correctly the columns that have NULL
> values.
You need to use LEFT OUTER JOIN, supported in Postgres 7.1.x.
See the current postgresql docs, or your favorite ANSI-SQL handbook, for
guidance.
-Josh Berkus
__AGLIO DATABASE SOLU
e,start_time) Values
> (5400,Welding,06:00:00);
Because you've forgotten the quote marks.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data manageme
-to address -- thus forcing me to reply to
the list.
> Gonzo,
>
> Maybe that question belongs in the pgsql-novice list instead. This
> list is
> for the experts like myself.
Hey, you're right. I'll use something similar to that reply next time.
ondary HR and billing
addresses.
Your third alternative is to create a JOIN table called Default Shops.
However, this does not really provide you any additional referential
integrity -- it jsut may suit you if you find triggers intimidating.
-Josh
__AGLIO DATABASE SOLUTIONS_____
TERVAL,
which will recognize '# days' as a valid expression.
IMHO, you've been lucky being able to skip the parens and CASTs so far;
get used to using them.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Com
.. update table1 set table1.col1=value
Because UPDATES, per the SQL 92 standard, are on one table only. Thus
any refenced columns *must* belong to that table, and if so, why name
it?
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
ERT/UPDATE operation inside a function, does it
automatically wait until the function completes before checking
constraints?
> Josh, maybe you should buy a newer SQL-bo... :-)
>
> Got ya (LOL)!
Zap! Ouch. ;-)
>
> The point is that we based our implementation of fore
rent names that come up as
identical?
3. Can anyone turn this function on its head, and rather than having it
accept 2 first/last names and a looseness factor and return TRUE/FALSE,
have it accept the two name pairs and return a looseness factor?
Go for it!
-Josh Berkus
__AGLIO
o monkey around
with custom triggers.
> 2. Can a column reference another column in the same table? eg..
>
> CREATE TABLE bloo (
> id int4,
> p_id int4 REFERENCES bloo (id)
> -- or
> --p_id int4 REFERENCES (id)
> )
Er ... why w
ented)
language instead. Java, Python, Perl and even 4GL would provide you
with more robust functionality.
For example, what you want is easily done in Java just by passing the
input parameters as By Reference.
Opinions on Middleware languages, anyone?
-Josh Berkus
__AGLIO DATABASE SOLUTI
FERENCES ... you do it with a CHECK CONSTRAINT
(look it up in the docs).
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (
(SELECT max(sortby) as maxsort, fk
FROM test
GROUP BY fk) maxtest
WHERE test.fk = maxtest.fk
AND test.sortby = maxtest.maxsort;
There's also a pgsql extension called SELECT DISTINCT ON, but it's not
SQL 92 standard so I don't reccomend it.
-Josh
__A
ight are in the
same table and are in a different table from banana_data and apple_data.
Or, perhaps, as a very advanced user, I'm just making some very advanced
mistakes ...
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
ming that
PictureID is the unique index of Pictures, are completely superfluous
and will only slow the query down. Particularly the use of DISTINCT in
a subquery should only be used if the contents of the subquery will be
displayed as part of the result set.
-Josh Berkus
__AGLIO DATABASE SOL
Tom, Stephan,
SHould I be concerned about this?
DEBUG: geqo_main: using edge recombination crossover [ERX]
DEBUG: geqo_main: using edge recombination crossover [ERX]
It happens when I run a truly massive (>2000 chars) query ...
-Josh
__AGLIO DATAB
struction test" for the GEQO, hey? The
only thing I'm missing is a couple of LEFT OUTER JOINS and maybe a WHERE
NOT EXISTS.
-Josh
P.S. The purpose of the view is to "flatten" a large chunk of complex
relational data into a comma-delimited text table to be
for which you have the skill and the time. The advisability of
doing so, however, is another thing entirely ...
Given that all of the MS SQL Server "peculiarities" are not ANSI SQL
standard, have you given any thought to porting by search-and-replace
script instead of trying to make
ing itself once it's in heavy use. I
though that MVCC was supposed to supercede deadlocks, unless I
specifically set locks, which I'm not.
Help, anyone?
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
ound the problem after 3 hours and a phone call to PostgreSQL Inc.
Turns out that I had one too-broad UPDATE in a 250-line function ...
feh!
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology
;"
Unfortunately, PostgreSQL functions do not yet return result sets. This
is on the "todo" list but unfortunately requires an overhaul of how
postgresql functions work.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Jo
AR + VARCHAR. So how is defining it as a concatination operator
(whatever other problems there might be with that) "overloading"?
Or am I missing the point?
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
201 - 300 of 864 matches
Mail list logo
