Forget that message. I need another cup of coffee!
"Jeff Eckermann" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> "Muhammad Nadeem Ashraf" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
>> Hi,
>> I am new user of
"Zlatko Matiæ" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>I am currently migrating from MSDE to PostgreSQL and have to rewrite the
>function that is calculating next date of sampling...
> In MSDE there is a DateAdd function. I can't find the appropriate function
> in postgre.
"Muhammad Nadeem Ashraf" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi,
> I am new user of PostGreSQL 8.0.1. While using it i faced following issue.
> As SQL is Case insensetive Language So the Uper or Lower cases are not
> significant. But while using the database there is pr
MS Query is crippled. If you want to do much from Excel, you will need to
write code. But it's not difficult. ADO is a good choice for this.
"gad renert via DBMonster.com" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hello, I am a novice with SQL, I have 2 tables
> (transfer
How are you getting the data from Excel? Perhaps you could use Excel's own
methods to evaluate the cell contents? You may still need to do something
for literal text values (e.g. 'NULL'), though.
"Kieran Ashley" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
Hi,
Apologies if th
--- Rodrigo Carvalhaes <[EMAIL PROTECTED]> wrote:
> Hi !
>
> I am quite confused of the results on a SELECT
> max...
>
> My environment:
> Conectiva Linux 10, PostgreSQL 7.4.6 (compiled from
> the sources)
>
> My problem is the "select max(id) FROM test" the
> result is 20 but the
> right is 1
--- Kent Anderson <[EMAIL PROTECTED]> wrote:
> I am attempting to use a stored procedure to pull a
> report from the
> database. My questions is if its even possible to
> pull the data using a
> function and then treat the returned data as a
> normal recordset with the web
> pages.
>
> The actua
--- Christopher Browne <[EMAIL PROTECTED]> wrote:
> In an attempt to throw the authorities off his
> trail, [EMAIL PROTECTED] ("Sandeep Gaikwad")
> transmitted:
> > Hello Sir,
> > I want to know how to check
> whether postgres database
> > is running or not ? when I give command
Ok, how about this. At least it works in my testing.
I have extended it to allow a negative sign (trailing
also), which I would expect to be allowed in a
comprehensive "isnumeric" function. If I am wrong,
feel free to slap me around; although correcting the
regex would be more constructive. ;-)
--- Joe Conway <[EMAIL PROTECTED]> wrote:
> Devin Whalen wrote:
> > First line:
> > my @active_tables=split(/,/,$tables);
> >
> > Is there anyway to split a variable like the perl
> split above?
>
> I'm no perl guru, but in 7.4 I believe this does
> what you're looking for:
>
> regression=# sele
s not changing, this will not tell
you anything, even if the user is in fact supplying
the name.
Depending on what rules you want to enforce, you may
be better off doing the checking in your application.
>
> >From: Jeff Eckermann <[EMAIL PROTECTED]>
> >To: Bernard Cheung <[
--- Bernard Cheung <[EMAIL PROTECTED]> wrote:
> I am writing a trigger function. How can I know
> which fields are being
> updated in the PL/SQL function?
>
> For example I have a table here:
>
>
> CREATE TABLE COMPANY (
> COMPANY_ID VARCHAR(10) NOT NULL,
> NAME VARCHAR(30),
> ADDRESS VA
;^[0-9]+(\\.[0-9]+)?$'').
If you are not familiar with regular expressions,
consider spending a little time learning about them.
They are simply the best way to do pattern matching,
and if you do any amount of text processing at all,
time spent learning will repay you very well.
>
--- Yudie <[EMAIL PROTECTED]> wrote:
> What is isnumeric function in postgresql?
> I'm using psql version 7.2.2
> thanks
> Yudie
I don't think that function is included as such. But
you could do something like:
CREATE FUNCTION isnumeric(text) RETURNS boolean AS '
SELECT $1 ~ ''^[0-9]+$''
' LANG
hat tutorial once upon a time. A very
good thing.
But I don't see the connection to PostgreSQL. Perhaps
you can explain it?
> - Original Message -
> From: "Jeff Eckermann" <[EMAIL PROTECTED]>
> To: "Ron M." <[EMAIL PROTECTED]>;
> <[
What interface are you using?
--- "Ron M." <[EMAIL PROTECTED]> wrote:
> I'm JUST getting started with the online SQL
> tutorial at
> http://sqlcourse.com. When I create a table and
> insert data, the data
> appears TWICE. A simple example:
>
> ***Create the table:
>
> create table rnmrgntable
>
--- Jon Poulton <[EMAIL PROTECTED]> wrote:
> Hi there,
> Im having a go at writing my first set of triggers
> for postgres and Im
> having trouble with an error message which the
> trigger produces when it
> tries to compile/call the function Ive written in
> pgsql. The error message
> is:
>
> ERR
--- Andrew Sullivan <[EMAIL PROTECTED]> wrote:
> On Tue, Mar 16, 2004 at 03:13:38PM +0530, Kumar
> wrote:
> > Dear Friends,
> >
> > Is possible to import data from MS Excel sheet
> into postgres
> > database 7.3.4 running on Linux 7.2
>
> Yes. I find the easiest way is to export a
> delimited fi
--- [EMAIL PROTECTED] wrote:
> Hi ,
>
>
>does postgresql support for the sql loader
> functionality like oracle
> does ??? if no, is there any tools specific to do
> this task connect to
> postgresql database???
> i'm looking into open-source tools that can do
> import & export facility.
>
--- Guy Fraser <[EMAIL PROTECTED]> wrote:
> Kenneth Gonsalves wrote:
>
> >On Tuesday 20 January 2004 19:26, Chris Bowlby
> wrote:
> >
> >
> >>Hi Ken,
> >>
> >> Under 7.3.x this option was removed, you need to
> test via:
> >>
> >> SELECT * from table where field IS NULL;
> >>
> >>
> >thanx -
--- Richard Huxton <[EMAIL PROTECTED]> wrote:
> On Friday 09 January 2004 02:13, [EMAIL PROTECTED]
> wrote:
> > Hi,
> >
> >
> > i try to load data from flat file (comma
> delimiter format) into
> > temporary table . i use COPY command as below:
> >
> > dwnc=# copy biosadm.custdo_temp
> > dwnc-#
--- "Stephen J. Thompson" <[EMAIL PROTECTED]>
wrote:
> But if I use it in a before trigger the procedure
> fails to work.
>
> CREATE OR REPLACE FUNCTION
> staging.write_work_country()
> RETURNS trigger AS
> '
> DECLARE
> alias_rec RECORD;
> BEGIN
> -- Cl
7.4 uses a completely new regex engine, so comparisons
with that will not be valid anyway.
Pre-7.4 regex behaviour is all documented:
Users Guide -> Functions & Operators -> Pattern
Matching
--- "scott.marlowe" <[EMAIL PROTECTED]> wrote:
> On 24 Oct 2003, Manuel Sugawara wrote:
>
> > Matias Surdi
--- Rod Taylor <[EMAIL PROTECTED]> wrote:
> On Fri, 2003-09-05 at 00:24, Jomon Skariah wrote:
> >
> > Hi,
> >
> > Thanks for your replys.
> >
> > We are facing another problem now.
> >
> > we need to find an alternative for Oracle's
> ADD_MONTHS in PostGres..
>
> Guessing based on the name th
--- Josh Berkus <[EMAIL PROTECTED]> wrote:
> Stephan, Tom:
>
> Hey, I have a function which can involve some
> records being deleted at the
> end. The user calling the function has permission
> to delete records (and
> I've tested this), but when I run the function I get
> "permission denied".
--- Rodger Donaldson <[EMAIL PROTECTED]> wrote:
> On Mon, Jun 16, 2003 at 05:02:34PM -0500, Bu, Ning
> wrote:
>
> > I have a field set up as money and I try to
> calculate some number
> > and put in this field, but the number will be 6
> demical point and I
> > want to cut it to only 2-4 decimal p
I believe that if you return NULL from the trigger
function, the delete will not actually be done. You
could set the value of the delete flag by reference to
OLD.fieldname.
I have not actually done this myself, so caveat
emptor.
--- Albrecht Berger <[EMAIL PROTECTED]> wrote:
> Hello,
> I need a
--- Susan Hoddinott <[EMAIL PROTECTED]> wrote:
> Hello,
>
> Having scoured the relevant documentation I cannot
> find anything which indicates how I simply create a
> database trigger to insert into a second table after
> insert on a first table, without the use of a
> procedure. As I do not want
--- william windels <[EMAIL PROTECTED]>
wrote:
> how can I convert sql-code , for microsoft sql
> server 2000, to sql-code for
> postgresql?
>
In addition to the suggestions given in other replies,
have a look at PGAdminII:
http://pgadmin.postgresql.org
That is an excellent utility, and comes wit
--- Oliver Vecernik <[EMAIL PROTECTED]> wrote:
> Is there a direct way to import such files into
> PostgreSQL?
>
As I believe others have replied: no, not yet.
If you are absolutely sure that your data will _never_
contain commas, then the simple solution of just
deleting all of the quotes , the
--- jack <[EMAIL PROTECTED]> wrote:
> Hi, all
> I'm using postgreSQL 7.2.3. The following statement
> always cuases a parser
> error, "parse error at or near NOT". Please adivse,
> thank you in advance.
>
> ALTER TABLE _acct_group1
> ALTER groupkey SET NOT NULL;
I believe you will need to u
What version are you using? I think "replace" is new
for version 7.3.
As another poster has pointed out, "translate" works
for individual characters. Or you could code up your
own replace using the native functionality of pl/perl
or pl/tcl or such. Or you could just upgrade to 7.3.
--- Andy Mor
--- Gaetano Mendola <[EMAIL PROTECTED]> wrote:
>
> "Gaetano Mendola" <[EMAIL PROTECTED]> wrote in
> message
> alq3mr$2s7o$[EMAIL PROTECTED]">news:alq3mr$2s7o$[EMAIL PROTECTED]...
> > Hi all,
> >
> > Suppose that I have a table like this:
> >
> >
> > att_1 |att_2 |att_3 | att_4
> >
--- [EMAIL PROTECTED] wrote:
> I need to sort the query by the log_date desc
> (log_date is char(10)). I
> tried the following without success :
>
> select id, log_date from userlog order by cast
> (log_date as date) desc
>
> select id, log_date from userlog order by
> date(log_date) desc
>
>
--- friedrich nietzsche <[EMAIL PROTECTED]>
wrote:
> Hi all,
> I'm in trouble with the same problem, but in PHP..
> With your solution, I cannot be totally sure that
> last
> inserted raw was mine...
> Because I'm on a web page, it could be that, as soon
> as I've inserted my record, another one
I'll second the suggestion about looking at Oracle
PL/SQL materials. I used an introductory PL/SQL text
when learning about PLpgSQL, and found it very useful.
The syntax/functionality is highly similar (by design,
apparently). Much of the Oracle PL/SQL functionality
is not (yet) implemented in P
try:
f_count_var := count(empno) from employee;
--- Joseph Syjuco <[EMAIL PROTECTED]> wrote:
>
> i want to put my count() result in a plpgsql
> declared integer variable
>
>
> declare f_count_var integer;
> begin
> select into f_count_var count(empno) from employee
> end;
>
> tried this
--- James Orr <[EMAIL PROTECTED]> wrote:
> I think he might be talking about mine. The region
> values will not be
> duplicated, the WHERE clause prevents it.
If you are saying that I didn't read the original
query closely enough, you're probably right.
Unfortunately I deleted the original me
Josh,
I don't see how you got the result you quote below.
My query sums the volume figures, grouping by scanid:
that worked as expected (one line per scanid) on my
system when I tested it (version 7.1.2, though I don't
think that matters).
Jeff
--- Josh Berkus <[EMAIL PROTECTED]> wrote:
> Of co
Maybe the reason is that you defined the function to return bool, but are
attempting to return text?
You need conditional logic, which suggests pl/pgsql is the way to go.
Something simple should do it, like:
create function check_loan (text,int,int)
returns bool
as '
begin
select * from loans whe
If you want "the third maximum value", easist to do:
SELECT * FROM table ORDER BY whatever DESC OFFSET 2 LIMIT 1;
- Original Message -
From: "Yoann" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, September 14, 2001 7:05 AM
Subject: Number the lines
> how can I number the res
You need to qualify "DENOM" with the table name: just write "xi.DENOM".
I find this to be good general practice when selecting from more than one
table.
- Original Message -
From: "Olle Wijk" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, September 12, 2001 4:06 AM
Subject:
I learned SQL from Sam's "Teach Yourself SQL in 21 Days", and am happy to
recommend it. Which book is "best" is very subjective and situation
dependent; all I can say is that this one did the job for me.
Pros: clearly written; knowledgable authors; good coverage
Cons: all due to space limitations
a) Look at "Date/Time Functions" in the docs:
specifically, listed under "Functions and Operators" in the Users
Guide.
b) "Serial" type will do this. Look under
"Numeric Types" , in the "Data Types" section of the Users Guide.
- Original Message -
From:
Jaydip
To: [EMAIL
SELECT *
FROM table
ORDER BY field DESC
LIMIT 1
OFFSET 1;
This way you don't need to know the value of "n" in advance.
The descending ORDER BY is to indicate a reversal of your intended ordering,
so as to make the n-1'th record the second record.
Note that getting the n-1'th record from an unorde
Not amongst the builtin functions. You will need to create your own using a
procedural language. Easiest is plperl, since Perl already has a very
functional "split" function.
- Original Message -
From: "omid omoomi" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, August 20, 20
Select id from T where name = 'bleh'
UNION ALL
Select id from T where description = 'bleh';
Will get you the resultset you want, but: I don't believe that you can do a
GROUP BY on it.
If you just want counts, as you describe below, you could do something like:
SELECT 'Only One', (SELECT count (i
UPDATE TableA SET nrA = TableB.nrB WHERE nrA =
TableB.nrA;
UPDATE statements don't take FROM
clauses. I don't think they like aliasing, either.
- Original Message -
From:
Fredrik
Thunberg
To: [EMAIL PROTECTED]
Sent: Monday, August 20, 2001 6:15
AM
Subject: Sim
I have encountered this problem (in a different context), and could not find
a way to insert entire rows/records in the way that you appear to want. But
it worked fine if I INSERTed explicitly, like:
INSERT INTO table VALUES (OLD.field1, OLD.field2, ..., OLD.fieldn);
That should work fine inside
Why not just include examples of DATEDIFF and DATEADD functions?
For example:
CREATE FUNCTION datediff(timestamp, timestamp)
RETURNS integer AS '
BEGIN
RETURN $2 - $1;
END;
' LANGUAGE 'plpgsql';
And similarly with DATEADD.
You will increase the scope (and length) of your article, but only slightly
The "create function" syntax is the same. The language syntax is straight
SQL:
CREATE FUNCTION getteamno(int4) RETURNS varchar AS '
SELECT tregion || ''/'' || to_char(tnumber, ''FM000'')
FROM teams
WHERE tid = $1
' LANGUAGE 'sql';
You don't get to build in error checking, but there has to be a
day, August 08, 2001 9:41 AM
> To: [EMAIL PROTECTED]
> Subject: Re: RE: Referencing named attribute in where clause doesn't
> work with7.1.2?
>
> Jeff Eckermann wrote:
> >
> > The WHERE clause is evaluated before your SELECT list is determined, so
> the
The WHERE clause is evaluated before your SELECT list is determined, so the
aliased value cannot be used.
You can put further NOT NULL tests into the subqueries to make sure that
null values are not returned.
Question: why not just join the tables explicitly? The more usual SQL
approach would be
With version 7.2 we will have pl/perlu (untrusted), which will allow use of
the various Perl modules which do this sort of thing.
> -Original Message-
> From: Josh Berkus [SMTP:[EMAIL PROTECTED]]
> Sent: Tuesday, July 31, 2001 1:16 PM
> To: Joe Conway; Bruce Momjian
> Cc: Josh Berkus;
You are confusing the syntax of two similar functions:
position ('substring' in 'string')
strpos ('string', 'substring').
I have a feeling that "position" actually calls "strpos", but I am guessing
on that.
> -Original Message-
> From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> Sent: Mo
The content of your record is not limited to user data. Including a field
that provides a unique key is simple: look at the documentation for data
type SERIAL for an easy way to do this.
You can also include information about when the record was inserted, and by
whom, just by including fields in
You will need to use "EXECUTE" to create the sequence. The docs on pl/pgsql
cover it: basically, "EXECUTE string" will cause that string to be executed
as a SQL statement.
> -Original Message-
> From: Dado Feigenblatt [SMTP:[EMAIL PROTECTED]]
> Sent: Friday, July 20, 2001 2:26 PM
> To:
You could make your FROM clause something like:
FROM members m
INNER JOIN address a ON m.madd = a.aid
INNER JOIN teams t ON m.team = t.tid
LEFT JOIN emails e ON m.memail = e.eid
I think that should work: if not, try putting everything between (but not
including) FROM and LE
If you include "oid" in your GROUP BY clause, you will get each distinct
record.
That will get you by for right now, but Josh's point is correct. You need
some kind of unique key in your table.
But... if you want to see every distinct record: why are you using a GROUP
BY?
> -Original Message
If the string will always be in that general form, use substring & position
functions (see "String Functions and Operators" in the docs.
Example: unit_number := substr(team_number, strpos(team_number, ''-'') + 1);
If you don't want the leading zero, you could make make the "+1" into "+2".
If you m
I think you need to use syntax:
raise exception ''Member % Not Found'', unitno;
> -Original Message-
> From: Gary Stainburn [SMTP:[EMAIL PROTECTED]]
> Sent: Wednesday, July 18, 2001 10:24 AM
> To: pgsql-sql
> Subject: Re: pl/pgsql - code review + question
>
> Okay, I've been hit r
I haven't seen anything on this list to say that pl/perlu is being worked
on. Is it?
> -Original Message-
> From: Alex Pilosov [SMTP:[EMAIL PROTECTED]]
> Sent: Wednesday, July 11, 2001 10:03 AM
> To: Peter Eisentraut
> Cc: R Vijayanath; [EMAIL PROTECTED]
> Subject: Re: can we wr
I don't think there is any way to do this using any of the generally
available tools.
You could do a workaround using pl/pgsql, in the following way:
* insert your data into a temp table
* output the data to a file using COPY
pl/pgsql allows INSERT statements, but does not allow CREA
???
I don't think anyone suggested adding a new builtin function.
Yes, your suggestion is good for an occasional use.
Where this kind of functionality is likely to be needed on a continuing
basis, my experience is that wrapping the code up in a custom function is
easier and cleaner than writing it
I assume that you want to do this within the database, i.e. with a query.
You would need to use a function, but I don't think a builtin function would
do it.
Try:
CREATE FUNCTION email_order (text) RETURNS text AS '
select substr ($1, strpos ($1, ''@'') + 1) || substr ($1, 1, strpos ($1,
''@'') -1
ically return
> NULL as the result, doesn't it?
>
> Albert.
>
>
> On Tue, Apr 10, 2001 at 05:41:26PM -0500, Jeff Eckermann wrote:
> > Regular expressions make this much easier. The below could be shortened
> to:
> >
> > create fun
And if you have plperl installed, something like this is even easier:
create function texttrim(text) returns text as '$_[0] =~ s/\\W//g; return
$_[0]' language 'plperl';
(I just read the docs) :-)
> -Original Message-
> From: Jeff Eckermann [SMTP:[EMAIL PROTECTE
Regular expressions make this much easier. The below could be shortened to:
create function ComparisonString(text) returns text as '
declare
t alias for $1;
r text;
c char;
begin
if t is null or t !~ ''[^a-zA-Z0-9
s
will become a problem.
> -Original Message-
> From: Jeff Eckermann
> Sent: Thursday, April 05, 2001 10:36 AM
> To: 'Gerald Gutierrez'; 'Andrew Perrin'
> Cc: PgSQL-SQL
> Subject: RE: [SQL] RE: serial type; race conditions
>
>
il 04, 2001 7:24 PM
> To: Jeff Eckermann; 'Andrew Perrin'
> Cc: PgSQL-SQL
> Subject: RE: [SQL] RE: serial type; race conditions
>
>
> It seems to just feel like conflicting requirements, so it's a tug-of-war.
>
> I've always done it by doing all t
Probably just me: but I don't see the point. Consider:
* User 1 commences insert transaction: grabs nextval(sequence),
max(foo)
* User 2 commences insert transaction: grabs nextval(sequence),
max(foo)
* User 1 commits
* User 2 commits (insert has sequence value one higher
I don't think this will necessarily work:
field1 | field2
aa | ab
a | aab
These are two distinct rows, so should be counted as two.
The proposed method would count them as one.
You can get around this problem by doing:
count (distinct (a || x || b))
where x is some character not foun
As a workaround, you can insert your row into an existing table, then
retrieve it from there later. I think you need to enumerate all of the
fields, as in 'INSERT INTO table VALUES (ret.field1,
ret.field2,...ret.fieldn);'. At least, I haven't succeeded any other way.
Messy, but the best method a
Still learning this stuff, so please be gentle...
jeff=# select * from test_it;
typ | diam
-+--
01 | 800
01 | 840
01 | 870
01 | 1120
02 | 760
02 | 780
02 | 800
02 | 900
03 | 1200
03 | 1234
03 | 1352
(11 rows)
jeff=# \! cat test_it
drop function test_it_too(text);
nsider
every mystery, I'd never get any work done:-).
Thanks very much for your help (again).
> -Original Message-
> From: Tom Lane [SMTP:[EMAIL PROTECTED]]
> Sent: Wednesday, February 07, 2001 12:48 PM
> To: Jeff Eckermann
> Cc: '[EMAIL PROTECTED]'
>
My script is below.
I thought (based on recent posts) that this use of RETURN is allowed, but
when trying an insert to report_table, I get the following error:
ERROR: control reaches end of trigger procedure without RETURN
I have solved several problems in getting to this point, but have now r
76 matches
Mail list logo
