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
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
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
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
_ _ _ _ _ _
Stehule wrote:
2011/2/22 Ralph Smith rsm...@10kinfo.com:
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
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
again.
Ralph
Tom Lane wrote:
Ralph Smith rsm...@10kinfo.com 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 similar to
''%STE[A-Z]*[0-9
= 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 have time to look
closely), but...
PERFORM Rnotice1(1,''SQL cmd
, 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 have time to look
closely
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_word
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.
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
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 stats where
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
=
;
END ;
$$ LANGUAGE PLPGSQL ;/* time_to_utime */
Any clues?
THANKS!
Ralph Smith
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
Character: 19001
-- referring to the semi-colon after the 'END
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
%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_next_delim('ralph smith','3') ;
NOTICE: achar is RrS
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
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 ;
Usecs := EXTRACT(EPOCH FROM TIMESTAMP
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 = %', date_string ;
good_date := to_timestamp
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: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Script errors on run
Same
, 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(EPOCH FROM DATE 'good_date') ;
You want something
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 info on HOW TO USE
...
Thanks all,
Ralph Smith
[EMAIL PROTECTED]
=
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
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 other
users
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 lower or use
quotes
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 chapters of the 8.2
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 Privileges
Chapter 20 Client Authorization
password prompt.
e.g.: psql -U username dbname
Thank you!
Ralph Smith
=
password prompt.
e.g.: psql -U username dbname
Thank you!
Ralph Smith
=
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
=
/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 free space map
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|
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
/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 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
Works for me. What does
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 wrong
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?
postgres=# select * from
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/postgresql/7.4/bin
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 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 surprising, seeing that you're
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, Scott Marlowe wrote:
On 10
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 Smith
[EMAIL PROTECTED
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 right
that it was either
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]
=
, what do I drop before I can start over w/ an `initdb -
E SQL_ASCII`
THANKS!
Ralph Smith
[EMAIL PROTECTED]
=
guess) the
'postgres' database instead of 'airburst'?
B) Once I get back there, what do I need to do besides
a)CREATE DATABASE (with SQL_ASCII 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
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]
=
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]
=
:::::::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
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]
=
-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]
=
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) |
status
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 launchd was released.
I
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 manually just
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
59 matches
Mail list logo
