There seems to be inadequate info on filling arrays from a table, (vs
keyboarding), and
how to do a lookup in an array. I've tried to almost no avail.
- - - - - - -
I have a loop that I'm executing thousands of times and inside of it I have
SELECT INTO myvar column1 FROM mytable WHE
Pavel Stehule wrote:
2011/2/22 Ralph Smith :
Hi,
I'm passing a tablename and two columnnames into a function so that I can
SELECT Records in a FOR LOOP using 'fixed' field names.
Using the passed params I construct the create table command and then alter
the names of two columns.
Here's what I'm doing.
It is to work on existing tables (not triggerable), but for subsequent
updates to the table(s) that I'm tokenizing fields for, a trigger will
be used to do the tokenizing of new data
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
_ _ _ _ _ _
Hi,
I'm passing a tablename and two columnnames into a function so that I
can SELECT Records in a FOR LOOP using 'fixed' field names.
Using the passed params I construct the create table command and then
alter the names of two columns.
When I 'build' the function and then run my query to use
e that one again.
Ralph
Tom Lane wrote:
Ralph Smith writes:
Within the function I have:
for darec in select * from purchbt where addr not like ''%STE%STE%'' and
addr not like ''%STE%STE%STE%'' and (addr s
Within the function I have:
for darec in select * from purchbt where addr not like ''%STE%STE%'' and
addr not like ''%STE%STE%STE%'' and (addr similar to
''%STE[A-Z]*[0-9]+'' or addr similar to ''%STE[A-Z]*[0-9]+[A-Z]'' or
addr similar to ''%STE[A-Z]*[0-9]+[A-Z][A-Z]'') loop
and it finds n
SERT Count = 33, Word2 = COLLEEN
---
Alban Hertroys wrote:
On 9 Nov 2010, at 5:11, Ralph Smith wrote:
Why is FOUND 'finding' and hence avoiding an INSERT?
Not really sure what your point is (don't ha
SERT Count = 33, Word2 = COLLEEN
---
Alban Hertroys wrote:
On 9 Nov 2010, at 5:11, Ralph Smith wrote:
Why is FOUND 'finding' and hence avoiding an INSERT?
Not really sure what your point is (don't h
How is "COLLEEN" not there and there at the same time?
-
NOTICE: did not = 11K = 42
CONTEXT: PL/pgSQL function "get_word" line 37 at perform
NOTICE: value = COLLEEN
CONTEXT: PL/pgSQL function "get_w
Yes, I'm using 7.4,12, I can't help it.
Is there a way to get the count of parameters to avoid overloading?
Thanks!
--
?* Ralph Smith*
Data Architech
As someone famous once said:
The documentation I've found on \copy says to see the docs on copy ;)
It's *supposed* to match the backend syntax, but seems a few bricks
shy of a load at the moment. Anyone feel like fixing it? See
parse_slash_copy() in src/bin/psql/copy.c.
Th
On Sep 23, 2008, at 7:50 PM, Tom Lane wrote:
Ralph Smith <[EMAIL PROTECTED]> writes:
I've written several user-defined functions (UDFs) for converting
dates to unix time, every which way.
... but when I try to use the function in a query
# select count(distinct username) from
Ralph Smith <[EMAIL PROTECTED]> writes:
I've written several user-defined functions (UDFs) for converting
dates to unix time, every which way.
... but when I try to use the function in a query
# select count(distinct username) from stats where eventtime >
dtu_dmony('
'22 Sep 2008') ;
it never comes back...
---
Is it the table-like formatting that's killing me?
How do I get around this?
Thanks!
Ralph Smith
smithrn at here washington.edu
=
regoire
Information Manager
www.boreal-is.com
Ralph Smith wrote:
I'm baffled and have tried various variations but still nogo.
From PgAdmin III I get:
---
** Error **
ERROR: syntax error at or near ";"
SQL state: 42601
Cha
rt(invar,':',3) ;
numberC := to_number(digitsC,'99') ;
result := 3600*numberA + 60*numberB + numberC ;
RETURN result ;
END ;
$$ LANGUAGE PLPGSQL ;/* time_to_utime */
Any clues?
THANKS!
Ralph Smith
I never did get an answer to this.
I get:
ERROR: cannot change return type of existing function
HINT: Use DROP FUNCTION first.
** Error **
ERROR: cannot change return type of existing function
SQL state: 42P13
Hint: Use DROP FUNCTION first.
When I try to:
CREATE OR REPLAC
;
RAISE NOTICE 'achar is R%S',achar ;
IF strpos(delimlist,achar) <> 0 THEN
RETURN j ;
END IF ;
END LOOP ;
RETURN 0 ;
END ;
$$ LANGUAGE plpgsql ; /* find_next_delim */
WHAT'S HAPPENING:
===
airburst=# select find_nex
at 4:34 PM, Stephan Szabo wrote:
On Wed, 4 Jun 2008, Ralph Smith wrote:
-- ==
good_date := to_date(year||'-'||month||'-'||day , '-MM-DD') ;
RAISE INFO 'good_date = %', good_date ;
UsecsD := EXTRACT(E
like programming javascript. The code
is right but the interpreter doesn't think so.)
Thanks all,
Ralph
==
On Jun 4, 2008, at 4:18 PM, GW wrote:
-Original Message-
From: [EMAIL PROTECTED] on behalf of Ralph Smith
Sent: Wed 6/4/2008 4:04 PM
To: pgs
Same problem, see below
On Jun 4, 2008, at 3:30 PM, Stephan Szabo wrote:
On Wed, 4 Jun 2008, Ralph Smith wrote:
date_string := to_date(year||'-'||month||'-'||day , '-MM-DD') ;
RAISE INFO 'date_string = %', d
On Jun 4, 2008, at 2:56 PM, Stephan Szabo wrote:
On Wed, 4 Jun 2008, Ralph Smith wrote:
-- ==
good_date := to_date(year||'-'||month||'-'||day , '-MM-DD') ;
RAISE NOTICE 'good_date = %',good_date ;
This is my first 'real' script, one that verifies proper format for a
user-entered date string.
Once that is done I want the script to return the UNIX time.
I plan on invoking this script on a psql connection (via .psqlrc), so
that I can call it from the command line.
Here's what I have at
Ralph Smith wrote:
I need to do a simple query and output to a file.
No problem.
But how do I encrypt one column's output?
There are lots of developer related links here, and info to "use
the /contrib pgcrypto", but I'm not a PG developer and I failed to
find any inf
hat library function...
Thanks all,
Ralph Smith
[EMAIL PROTECTED]
=
be you can smell the sulfur from where you are?
Ralph Smith
=
On Apr 4, 2008, at 2:57 PM, Tom Lane wrote:
Ralph Smith <[EMAIL PROTECTED]> writes:
However I'm now trying to access it using all lower case.
Are you saying that IT'S confused and I should go all l
On Apr 4, 2008, at 2:11 PM, Tom Lane wrote:
---
Ralph Smith <[EMAIL PROTECTED]> writes:
I just can't connect to a database, though I can to others as
I just can't connect to a database, though I can to others as other
users...
postgres=# CREATE ROLE xyz WITH password 'abc' ;
CREATE ROLE
postgres=# CREATE DATABASE LabNews_dev WITH OWNER=xyz ENCODING='UTF8' ;
CREATE DATABASE
postgres=# grant create, connect on database LabNews_dev to xyz wi
SUPER Eric! Very explanatory!
Thank you!
Ralph Smith
=
On Mar 6, 2008, at 10:17 AM, Erik Jones wrote:
On Mar 6, 2008, at 11:52 AM, Ralph Smith wrote:
Ralph Smith wrote:
> And should be easier to find in the manual!
>
> I've looked in many related c
Ralph Smith wrote:
> And should be easier to find in the manual!
>
> I've looked in many related chapters of the 8.2 manual for a way to
> find out
> WHY a specific user has access to a database.
>
> Chapter 5Data Definition
> Chapter 18 Database Roles &
tID range that requires a password.
But he can create and drop tables in any database!!!
Why is that?
How can I find out what he can do?
The GRANT and REVOKE sections say nothing about which pg_ tables
to query, and I've been lookin'!
Thank you!
Ralph Smith
=
o a particular DB w/o any
password prompt.
e.g.: psql -U username dbname
Thank you!
Ralph Smith
=
o a particular DB w/o any
password prompt.
e.g.: psql -U username dbname
Thank you!
Ralph Smith
=
200:00:00 /usr/lib/postgresql/
8.2/bin/postgres -D /var/lib/postgresql/8.2/main
Can anyone tell me what that is?
Thank you,
Ralph Smith
==
---(end of broadcast)---
TIP 5: don't forget to increase your
I'm not sure if you're saying I should ignore these errors...
I'm using dumps from DB airaburst.
>postgres=# \l
List of databases
Name| Owner | Encoding
+--+---
airburst | root | SQL_ASCII
lt_dev1| postgres | UTF8
lt_dev2| postgre
I'm looking at the v7.4 manuals and I don't see how to encode for
importing into a v8 DB using UTF8.
Maybe I'm making this hard on myself?
The old DB is using SQL_ASCII.
We'd like the new one to use UTF8.
As development proceeds, I'm going to have to do this regularly, both
the entire DB and
emove the test DB from 8.2 as the dumpall that loaded it was via 7.4
not 8.2
use 8.2's dumpall to dump the 7.4
use 8.2's psql to load in that dump
Ralph Smith
[EMAIL PROTECTED]
=
On Oct 25, 2007, at 1:57 PM, Tom Lane wrote:
Ralph Smith <[EMAIL PROTECTED]> writes:
On Oct 25, 2007, at 1:09 PM, Tom Lane wrote:
Ummm ... those are the column headings for 8.2 pg_database, not 7.4.
You're looking at the wrong postmaster.
[EMAIL PROTECTED]:/usr/lib/postgre
On Oct 25, 2007, at 1:09 PM, Tom Lane wrote:
Ralph Smith <[EMAIL PROTECTED]> writes:
On Oct 25, 2007, at 12:24 PM, Tom Lane wrote:
Hmph. Nothing obviously wrong there, except that it's not finding
anything except template1. What does "select * from pg_database"
show?
On Oct 25, 2007, at 12:24 PM, Tom Lane wrote:
Ralph Smith <[EMAIL PROTECTED]> writes:
On Oct 25, 2007, at 10:13 AM, Tom Lane wrote:
Works for me. What does the -v give you on stderr? Also,
7.4.what-exactly and 8.2.what-exactly?
Sorry for the bulk here...
Hmph. Nothing obviously
==
On Oct 25, 2007, at 10:13 AM, Tom Lane wrote:
Ralph Smith <[EMAIL PROTECTED]> writes:
I want to use v8.2's pg_dumpall to export v7.4's data into a text
file.
prompt:/usr/lib/postgresql/8.2/bin$ ./pg_dumpall -c -v -p 5433 >
myfile.txt
:/usr/lib/postgresql/8.2/bin$ ./pg_dumpall -c -v -p 5433 >
myfile.txt
It's NOT dumping everything. Only... Well, I'm not sure.
I think only DB postgres. It's only 87 lines long.
Anybody have any suggestions?
Thank you,
Ralph Smith
[EMAIL PROTECTED]
=
On Oct 19, 2007, at 8:25 PM, Tom Lane wrote:
Ralph Smith <[EMAIL PROTECTED]> writes:
When I:
[EMAIL PROTECTED]:/usr/lib/postgresql/7.4/bin$ psql -U
airburst airburst -p 5433
I get:
psql: FATAL: IDENT authentication failed for user "airburst"
This is not surpris
sages = debug1
log_min_messages = debug1
log_min_error_statement = error
YET I'm getting NO logging in either /var/log/syslog nor in /var/log/
postgresql/postgresql-7.4-main.log
WHY?
Why no logging
and Why unable to connect?
Ralph Smi
On 10/19/07, Ralph Smith <[EMAIL PROTECTED]> wrote:
Thank you Scott!
I'm away from my desk and will dive back into it.
Fortunately I have two machines, each w/ 7.4 & 8.2 on them. New
installs on Ubuntu 7.4.
As to why I had 'no role or database' errors yesterday, am I
dump when I thought I'd done a
pg_dumpall, or
B) Using the text file output of pg_dumpall behaves differently on
import than the -Fc format?
I'd imported it w/ psql, since it was a text file.
Thanks!
Ralph Smith
[EMAIL PROTECTED]
=
On Oct 19, 2007, at 1:35 PM,
umpall's
results?
I'd dump-all'd into a text file and imported it via a psql -f filename.
It had encoding errors AND did NOT make the database I needed.
Everything went into db postgres.
Please help.
Ralph Smith
[EMAIL PROTECTED]
=
encoding)
b) In that DB create the ROLE 'airburst'
c) ?
C) Then do I import the production version's dump?
Thanks again all,
Ralph Smith
[EMAIL PROTECTED]
=
over w/ a new install?
If the latter, what do I drop before I can start over w/ an `initdb -
E SQL_ASCII`
THANKS!
Ralph Smith
[EMAIL PROTECTED]
=
transaction log file
"0001007D"
2007-10-09 00:02:02 LOG: incomplete startup packet
2007-10-09 05:02:01 LOG: incomplete startup packet
2007-10-09 10:02:01 LOG: incomplete startup packet
Thank you!
Ralph Smith
[EMAIL PROTECTED]
=
he same version database that I want to access.
What is the best way to upgrade? Use pg_dumpall (using 8.2's
program), afterwards redirect that file into v8.2 via psql v8.2?
Many thanks!
Ralph Smith
[EMAIL PROTECTED]
=
l all ::1
:::::::md5
I thought local would allow me w/ 'all'.
None of the PG environment variables are set.
I thought they would be c/o the import all.
Where should I make them permanent?
Ralph Smith
[EMAIL PROTECTED]
=
In preparing to upgrade, (a long story), of our version 7.4 database
cluster,
I'm trying pg_restore using the file from pg_dumpall, also version 7.4
into a virgin install of PostgreSQL 7.4 on Ubuntu.
Postmaster is up and running (c/o ps -ef)
The error I get:
pg_restore: [archiver] input file do
ether NLS is wanted... no
checking for default port number... 5432
checking for gcc... gcc
checking for C compiler default output... configure: error: C
compiler cannot create executables
[EMAIL PROTECTED]:~/Desktop/postgresql-7.4.17$
-
Ralph Smith
[EMAIL PROTECTED]
=
mostly of near-sequential inserts into a
LARGE table, accompanied by updates into much smaller tables.
I perform the occasional queries on the large table, requiring
sequential scans.
Are there any indications whether 32 or 64 bit Linux would be
preferable?
Thanks all,
Ralph Smith
[EMAIL PROTECTED]
=
On Aug 13, 2007, at 4:39 PM, Scott Marlowe wrote:
On 8/13/07, Ralph Smith <[EMAIL PROTECTED]> wrote:
I'm confused. Shouldn't this index be used?
(It's running on v7.4.7)
airburst=> \d stats2
Table "public.stats2"
Column |
I'm confused. Shouldn't this index be used?
(It's running on v7.4.7)
airburst=> \d stats2
Table "public.stats2"
Column | Type | Modifiers
---+---+---
lab | character varying(30) |
name | character varying(50) |
stat
Apologies for the huge post, but it's got everything relevant that I can
think of.
See below.
Michael Glaesemann wrote:
On Jul 31, 2007, at 14:27 , Ralph Smith wrote:
=== INITIAL POST
I'm using scripts in /Library/StartupItems/PostgreSQL
PostgreSQL starts man
wn isn't clearing things up?
Or is the problem in the startup?
Thanks!
=== FIRST REPLY ===
Michael G wrote:
On Jul 30, 2007, at 16:14 , Ralph Smith wrote:
I'm using scripts in /Library/StartupItems/PostgreSQL
I haven't used a StartupItem for PostgreSQL since l
I'm using scripts in /Library/StartupItems/PostgreSQL
PostgreSQL starts manually just fine via
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l
/usr/local/pgsql/logfile start
PostgreSQL will not start on System restart using files in
/Library/StartupItems/PostgreSQL (See below).
If I
60 matches
Mail list logo
