Frankie wrote:
The case is when I call a function (from Server 1 at psql prompt) that will
call dblink to do some operation
on another server(it will take certain time), i.e. Server 2, and meanwhile I
just unplug the network cable to Server 2.
The consequence is that the function will never return
David Durst wrote:
Is there anywhere I can get these in binary?
Or is my only option to compile Postgres from source??
Depends on the type of binary. E.g. there is a
postgresql-contrib-7.3.1-1PGDG.i386.rpm binary available here:
ftp://ftp.us.postgresql.org/binary/v7.3.1/RPMS/redhat-7.3/
Joe
Larry Rosenman wrote:
--On Friday, January 31, 2003 01:34:42 -0800 David Durst
<[EMAIL PROTECTED]> wrote:
Does there exsist a MD5 Function I can call???
look at /contrib/pgcrypto in the source distribution.
Also worth noting is that 7.4 will have (and cvs HEAD has) a builtin md5 function:
r
Michiel Lange wrote:
Would the same work for pg_user and pg_group?
See:
http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=11378
With these groups:
regression=# select * from pg_group;
groname | grosysid |grolist
-+--+---
grp1|
Guy Fraser wrote:
This is what I want to do:
select attribute,array_list(values,1,sizeof(values)) as value from av_list;
Turn :
attr6 | {val3,val7,val4,val5}
Into :
attr6 | val3
attr6 | val7
attr6 | val4
attr6 | val5
You didn't mention the version of PostgreSQL. If you're using < 7.3, go
Achilleus Mantzios wrote:
On Tue, 7 Jan 2003, Tom Lane wrote:
Does your function know about filling in the elemtype field that was
recently added to struct ArrayType?
She has no clue :)
Any pointers would be great.
See construct_array() in src/backend/utils/adt/arrayfuncs.c.
HTH,
Joe
---
John Pauley wrote:
All, Any suggestions on a Postgresql equivalent to
the following DB2 sql query:
SELECT * FROM (SELECT
ID,NAME,CITY,STATE,PROGRAM,CUST_NAME,CUST_ID,ROWNUMBER()
OVER (ORDER BY ID ASC) AS RN FROM
MERCHANT_BROWSER_VIEW WHERE CUST_ID = 'A51B8CA2' ) AS
RESULT WHERE RN BETWEEN 1 AND
Elizabeth O'Neill's Office Mail wrote:
I have two tables in my database a complaint table and a resolution table.
One complaint may have several resolutions. I am trying to build a report
that will give me the complaint details and all the resolution descriptions
for a complaint in one text area/r
Dan Langille wrote:
Given that I'm considering adding a new field path_name to the tree,
I can't see the ltree package will give me anything more than I can
get from like. My main reason for adding path_name was doing queries
such as:
select * from tree where path_name like '/path/to/parent
Evgen Potemkin wrote:
Joe,
i've made it already,as send first 'Proposal ...' message,
but found a small bug. within nearest days i'll fix it, and post the patch
to pgsql-patches.
Please note that there was no patch attached to your initial proposal
(assuming this is the message you are referr
LR wrote:
Thank you for your answer.
Do you know the name of this package ?
Thx
Lilian
see contrib/dblink.
I'd highly recommend using 7.3 (finishing beta, soon to be a release
candidate) if you can. It is much better than the one in 7.2 and the syntax
has changed some.
Joe
---
reSQL 7.3, as a /contrib module, by Joe Conway.
Download 7.3b5 now if you can't wait; Joe would like more people to
test his module, anyway.
I have it on my personal TODO to approach this for 7.4, but I'll be happy to
focus on other things if you'd like to take this on. The conn
Richard Huxton wrote:
On Thursday 07 Nov 2002 11:47 am, Christoph Haller wrote:
Regarding to Terry's request on multiple aggregates and
Shahbaz's request for generating a cross tab ( pivot table )
in September, I've found an excellent example on a german inet page
http://www.itrain.de/
I've tran
Josh Berkus wrote:
Is it possible to create a view using tables from two different
postgresql
servers?
No.
It isn't possible with plain old SQL, but it is possible (albeit ugly) using
contrib/dblink in PostgreSQL 7.2.x. See README.dblink for documentation and
examples.
In PostgreSQL 7.3 (i
Scott Yaung wrote:
> I like to do something like this:(build a tree from relationship)
[snip]
> How can i make it by sql , and sql functions
> Thanks lot and regards.
There have been quite a few discussions of this topic in the past, so I would
suggest you search through the archives.
In 7.3 (c
Aasmund Midttun Godal wrote:
> It would be very usefull to have these in sql, so that it is even easier to create
>tables with encrypted passwords.
>
See contrib/pgcrypto
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives
Camila Rocha wrote:
> Is there a way to put in order the rows in a table? the problem is that i w=
> ant to keep a "tree" in the db, but the leaves must be ordered...
> does someone have an idea?
If you don't mind trying 7.3 beta, there is a function called connectby() in
contrib/tablefunc. It w
Frederic Logier wrote:
> Great ! have you some example for call a pl/perl function from a
> pl/pgsql function ?
I don't use PL/Perl, but I think you should be able to do:
SELECT INTO var my_plperl_func(text_to_split);
from within your PL/pgSQL function.
> And could i use an int array in pl/pg
[EMAIL PROTECTED] wrote:
> Ok, if this does not apply to versions prior to 7.3beta
> then what do I need to do if I am running 7.2.1? When I
> try to use the SETOF to retrun a row set, I only get
> one column.
First, prior to 7.3 there is no SCHEMA support in Postgres. Everything lives
in ess
david williams wrote:
> Also,
>
> the table definition MUST be in the Public Schema. I use my own schema
> names but in order for the table to be found by the function it ( the
> table ) must be in the public schema. Although it can be empty.
(Note:
this discussion does not apply to Postgre
Frederic Logier wrote:
> hi,
>
> i'm looking for a split function, like perl or php.
> I need doing a pl/pgsql function with a split return an array.
> I must do some loop with this array for mass inserting.
>
> I think of doing it with pl / perl but I need to do insert and I don't
> find exampl
Jeroen Olthof wrote:
> What is going wrong here?
>
> An example of what I'm trying to do.
>
> vw_teams is a view but same problem when trying it on a single table
> CREATE FUNCTION test() RETURNS SETOF vw_teams AS 'select * from vw_teams;'
> LANGUAGE 'sql';
>
> SELECT test();
>
> results in
>
Tim Perdue wrote:
> Hi Folks -
>
> I'm working on a global weather database for a client and have hit an issue
> which I'm sure has been solved before. Unfortunately, the site search doesn't
> pull up anything.
>
> Essentially, I've got two tables, one with city/county/lat/long and another
Beth Gatewood wrote:
> well, I know that I will have only a single sequence that will generate the
> primary key per table. So basically, this type of function, for me needs
> only to return the value of the primary key.
>
> I believe I mentioned in one of my posts the motivation behind not want
Josh Berkus wrote:
> Particularly, the difficulty is that this application gets many small
> requests during the day (100 simultaneous uses) and shares a server
> with Apache. So I have to be concerned about how much memory each
> connection soaks up, during the day. At night, the maintainence
Josh Berkus wrote:
> The problem: The update series (done as a function) takes 10-15
> minutes. During this time, the CPU is never more than 31% busy, only
> 256mb of 512 is in use, and the disk channel is only 25% - 50%
> saturated.As such, is seems like we could run things faster.
>
> What
Kristian Eide wrote:
> Thanks, very helpful, although there does not seem to be much description of
> what the two free space map options in postgresql.conf actually do. Doing a
> VACUUM ANALYZE VERBOSE on my largest table gives me:
>
> NOTICE: Pages 43681: Changed 208, Empty 0; Tup 1921387: Vac
Kristian Eide wrote:
> This is not good as the database is in use 24/7, and without the indexes
> everything comes to a screeching halt. This means I probably will have to
> stop the programs using the database for the time it takes to re-create the
> indexes; this is better than having to dump/re
Bhuvan A wrote:
> I am using postgresql 7.2.1.
> I badly require to interconnect between databases. contrib/dblink seems to
> be handy and ofcourse it well suits my requirement. But while browsing
> across, i heard that it is not advicable to use it. So i wish to know
> someone's experience in usi
David Durst wrote:
> I was wondering if there was a way of returning a complete row from a
> function, in reading the documentation of CREATE FUNCTION. I was under the
> impression that you could return a row by using setof, but this does not
> seem to be true.
> Can anyone help?
The short answer
Josh Berkus wrote:
> Folks,
>
> Hey, I need to write a date calculation function that calculates the
> date after a number of *workdays* from a specific date. I pretty
> much have the "skip holidays" part nailed down, but I don't have a
> really good way to skip all weekends in the calucla
Vernon Wu wrote:
> Command:
>
> Insert into profile (userid, haveChildren)values('id98', 'No');
>
> Error:
>
> ERROR: Relation 'profile' does not have attribute 'havaChildren'
^^^
From the error message, looks like you spelled haveChild
Christopher Sawtell wrote:
> Greetings folks,
>
> Please could a kind soul tell be how to extract data from a BYTEA type of
> field into a file, or better still tell me where I can find some doco?
Bruce has already written some minimal documentation which is in the 7.2
beta. I'm hoping t
> select ROUND(0.5) ; returns 0
> select ROUND(1.5) ; returns 2;
> select ROUND(2.5) ; returns 2;
> select ROUND(3.5) ; returns 4;so on .
> I'm sure you would have figured out what's happening !!! Why ??
> How do I get to approximate any number x.5 as x+1 ??
Looks like a bug to me:
test=# se
> Hi all,
>
> I need to implement "Find all hospitals in a 5 mile radius". Say I have
all
> the coordinates on them stored in a table with the fields longitude and
> latitude. Has anybody some sample code for that?
>
> Best regards,
> Chris
>
Here's a plpgsql function that will do what you need.
> im new in postgresql (actually came from SQL Server) and i was trying a
> script like this
>
> insert into table1(field1,field2) values (select field1, field2 from table
> 2);
>
> i dont know if this is possible (inserting a set of entries via resultset
> from a select stmt in one command). If
> The "Name Alike" PL/pgSQL function has been posted on Roberto Mello's
> cookbook:
>
>
http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=96
7
>
> This function requires Joe Conway's port of the Metaphone and
> Levenshtein functions to PostgreSQL, available from /contrib on
Subject: [SQL] Outer Join Syntax
> I'm doing a feasability study on porting our flagship product to Postgres
> (from MS_SQL). I have run across a few snags, the largest of which is the
> outer join syntax. MS has some nice syntactical sugar with the *=/=*
> operators that Postgres dosen't seem
> >
> > Actually, this may even be closer to what you want:
> > http://www.php.net/manual/en/function.levenshtein.php
>
> Hey, that's terrific! I didn't know that those programs existed
> outside fo expensive proprietary software.
>
> Now, who can I talk into porting them (metaphone, levenstein)
> > Sounds like you want something along the lines of soundex or metaphone?
I
> > don't see either function in PostgreSQL, but take a look at the PHP
manual
> > to see examples: http://www.php.net/manual/en/function.soundex.php ,
> > http://www.php.net/manual/en/function.metaphone.php
> >
>
> See
> And the fuzzy_match should return True if the two phrases are no more
> than that number of characters different. Thus, we should get:
>
> fuzzy_match('Thornton','Tornton',1) = TRUE
> fuzzy_match('Thornton','Torntin',1) = FALSE
> fuzzy_match('Thornton','Torntin',2) = TRUE
>
> Unfortunately, I c
> phones=# \d i_pl_pseq
> Index "i_pl_pseq"
> Attribute | Type
> ---+--
> entity| character varying(3)
> pseq | bigint
> btree
>
> phones=# explain select * from phonelog where entity = '001' and pseq >=
> 9120 and pseq <= 9123;
> NOTICE: QU
> > Now, i need to get the details of all employees who did
> > receive NONE of the salesorders. ie.. i wish to select the
> > records of table 'employee' whose 'emp_id' are not
> > there in table 'salesorder'.
> >
> > I need to accompolish in a single query!
This should work:
select e
> However, not all types of errors are so trapped. The most problematic
> un-trapped error is referential integrity: if an INSERT or UPDATE fails
> because of a referential integrity violation, the PL/pgSQL function will
> still see the statement as a success and not error out. Example:
>
I'm
> Thanks for your valuable information. I tried the
> cron. i typed
>cron -e
> and entereed into the input area. but i don't know how
> to save the cron file. I pressed ctrl+z and came out
> from cron. but i edit the cron file i found nothing on
> it.(i.e using pico filename.) Please tell me
> I am a user postgresql. I want to update a table
> automatically when we reach monthend. i.e i want to
> update some table on 31 of every month automatically
> without any user attention. I want to know how to do
> this. If anyone knows how to do this please mail me. i
> will be ever thankfu
> A select is done across two tables, however when joining the foreign
> key, the right hand side of the equallity has (+) appended
>
> SELECT o.* from one o, two t where o.key = t.key(+)
>
> Does anyone know what this does and how I can reproduce the select in
> PostgreSQL?
Hi Chris,
The (+) in
> You could send the column name directly into your c function. For
example:
> c_function_name(NEW.col1, NEW.col2, NEW.col3). Otherwise I am not sure
how
> to send NEW into a C function. You could try declaring NEW in your C
> function as a tuple.
Thanks for your reply. I was hoping that I cou
ssage 'NEW used in a non-rule query') or gain
> access to the trigger tuple from within my C function? It seems that
> although PLpgSQL is called as a trigger, the C function is called as a
> regular function (makes sense) and thus has no access to the trigger tuple
> (my problem).
&
Hi,
I've been searching the docs and been unable to find the answer to this --
is there a way to get the current database server host tcpip address,
postmaster port, and database name from a SQL query?
I'd like to access those from within a plpgsql function without having to
create and populate
Hello,
I was looking for a datatype to represent a single byte unsigned integer.
The closest thing I can find looking through the online manual is a one byte
char. Are there any side-effects of using a char datatype for this purpose?
Is there a better datatype to use?
Thanks in advance,
Joe
Subject: Re: [SQL] Optimization recommendations request
>
> What does explain show for your query?
>
I sent this a week ago using the wrong (i.e. not the one I signed up with)
reply-to address, so it didn't make it to the list until just now. In the
meantime I ran explain and noticed that the i
;
-- then tried
create index foo_idx1 on foo using HASH (guid);
SELECT ks FROM foo WHERE guid = 'f9ee1930f4010ba34cc5ca6efd27d988eb4f921d';
The query currently takes in excess of 40 seconds. I would appreciate any
suggestions for optimizing to bring this down substantially.
Thanks in advance,
Joe Conway
fd27d988eb4f921d';
>
> The query currently takes in excess of 40 seconds. I would appreciate any
> suggestions for optimizing to bring this down substantially.
>
> Thanks in advance,
>
> Joe Conway
>
> > On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows
use
> > of the up arrow key for history and the escape/tab key for command
> > completion, but on my remote web host (webpipe.net) those keys don't
work.
> > What do I need to do to get these features working on my remote
Hello,
I've been searching off-and-on for an answer to this question, but I haven't
found anything but fleeting references.
Here's the problem/question:
On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows use
of the up arrow key for history and the escape/tab key for command
101 - 156 of 156 matches
Mail list logo
