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
> > 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
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
>
;
-- 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
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
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
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
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).
&
> 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
> 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
> 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
> 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
> 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
> > 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
> 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
> 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
> > 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
> >
> > 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)
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
> 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
> 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
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
> 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.
> 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
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
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
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
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
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
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
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
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
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
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
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
>
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
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
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
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
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
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
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
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
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
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
---
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
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
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
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
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
---
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
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|
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
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
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
Danny Su wrote:
I am currently converting everything from SQL Server to PostgreSQL.
This is for an application that is going to support Oracle, SQL Server
and PostgreSQL at the same time. I have done a lot of the conversion
already but I am stuck on functions that returns parts of views or tab
Michael A Nachbaur wrote:
Source Target
[EMAIL PROTECTED] [EMAIL PROTECTED],[EMAIL PROTECTED],
It isn't clear from your description what you want (to me, anyway), but
I'd guess something like this:
regression=# select * from src2tgt;
source| target
-+
Mr Weinbach, Larry wrote:
But at execution time I am getting thi error :
WARNING: Error occurred while executing PL/pgSQL
function word_case
WARNING: line 5 at return next
ERROR: Set-valued function called in context that
cannot accept a set
You didn't show the execution time SQL statement, but
Radu-Adrian Popescu wrote:
.
begin
_res.code:=1;
select id into iid from log where id=_id;
if not found then begin
_res.msg:=''insert'';
*insert into log (log, data) values (_log, _data);
if not found* then begin
_res.msg:=_res.msg
Greg Stark wrote:
SELECT greatest(a,b) FROM bar
would return one tuple for every record in the table with a single value
representing the greater of bar.a and bar.b.
You could define your own functions to do this but it would be tiresome to
define one for every datatype.
In 7.4devel (just starting
Joe Conway wrote:
In 7.4devel (just starting beta) you can do this:
Actually to correct myself, we just started "feature freeze" for 7.4,
with beta planned to start on or about July 15th.
Sorry for any confusion caused.
Joe
---(end of
Josh Berkus wrote:
create or replace function greatest(anyelement, anyelement) returns
anyelement as 'select case when $1 > $2 then $1 else $2 end' language
'sql';
Way cool. I'd have to imagine that it would blow up if you did this, though:
select greatest ( 512, now() );
With an "Operator is no
Alon Noy wrote:
From what I tried it is possible to create such a function but it is not
possible to call it ?!
Can anyone provide an example?
create table foo (f1 int, f2 text);
insert into foo values(1,'a');
insert into foo values(2,'b');
insert into foo values(3,'c');
create or replace function
Ang Chin Han wrote:
Tom Lane wrote:
create function greatest(anyelement, anyelement) returns anyelement as
'select case when $1>$2 then $1 else $2 end' language sql;
Any chance of this making it into 7.4's contrib? Maybe with enough
documentation to make it a tutorial for PostgreSQL's user functi
Richard Rowell wrote:
Can I accomplish this with postgresql without involving an external
process (like say perl)? I guess I could put the upgrade stuff into
PL/SQL functions and just drop the functions when I'm done, but I was
hoping for something a little "cleaner".
There is no way (currently at
Luis Sousa wrote:
How can I set a function as setuid ?
I take a look at the documetation, on Reference Manual and the only
reference I saw to it was on SET SESSION AUTHORIZATION.
See:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-createfunction.html
CREATE [ OR REPLACE ] FUNC
Benoît Bournon wrote:
I have to make a function that returns a tree with title and link of a
table.
Recursively, a information depends on a parent information.
It is to organise a menu with parent dependance.
How is it possible and faster ? in C ? pl/pgsql or other ?
On 7.3 and later: see con
Steve Wampler wrote:
I've got a simple nested query:
select * from attributes where id in (select id from
attributes where (name='obsid') and (value='oid00066'));
that performs abysmally. I've heard this described as the
'classic WHERE IN' problem.
I may be missing something, but why can't
David Olbersen wrote:
Now the question: is there a single index I can create that will be
used when my WHERE clause contains either urlhost or urltld? I could
create two functional indexes, but that seems a bit silly to me.
I can't think of how to do only one index in 7.3.x and earlier, but
FWIW,
BenLaKnet wrote:
I see connect by in Oracle
??? is there an equivalent in PostgreSQL or not ??
Someone is working on the SQL99 equivalent, but it isn't done yet.
Perhaps for 7.5.
Joe
---(end of broadcast)---
TIP 8: explain analyze is your friend
Mike Rylander wrote:
I have a rather odd table structure that I would like to simplify to be a view
(for some definition of simplify). The current idea I have is to shovel
values from multiple rows in one table into an array in the view. The tables
look something like this:
Is anything like
Mike Rylander wrote:
Thank you! This is great news. Is there a projected release date for 7.4?
Not exactly an officially projected date, but in the past IIRC beta/RC
has lasted 2 to 3 months, so I'd start looking for a 7.4 release in October.
Also, is there a published roadmap, or should I just
Cristian Cappo wrote:
>>> select __function(10::int2, 20::int2)[1]
^^^ parsing error.
Try:
create or replace function foo(int2, int2 ) returns _varchar as '
select ''{1,2}''::_varchar
' language 'sql';
regression=# select (foo(10::int2, 20::int2))[1];
foo
-
1
(1 row)
Cristian Cappo A. wrote:
Tried, but...
>> select (foo(10::int2,20::int2))[1];
>> ERROR: parser: parse error at or near "[" at character 32
I'm using the version 7.3.3
Sorry, it works on 7.4devel, so I thought it might on 7.3 as well. In
any case, this works on 7.3.3:
test=# select f1[1] fro
Mark Bronnimann wrote:
I was hoping to eliminate the parse call on the view because I was doing
the where clause on the view instead of putting the where in the view.
In all, I was hoping to keep a single view called from multiple functions
with different where clauses. Yep... I shoulda known
Scott Cain wrote:
On Mon, 2003-08-04 at 11:53, Tom Lane wrote:
I find it really, really hard to believe that a crude reimplementation
in plpgsql of the TOAST concept could beat the built-in implementation
at all, let alone beat it by two orders of magnitude.
Either there's something unrealistic abo
Scott Cain wrote:
Oh, and I forgot to mention: it's highly compressed (bzip2 -9) and is
109M.
Thanks. I'll grab a copy from home later today and see if I can find
some time to poke at it.
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscr
Bertrand Petit wrote:
In two table I store "objects" and their attributes. The
attributes, which are not meant to be searched, are stored as unbound
arrays of varchars. I have a query that needs to use those attributes
on both sides of an EXCEPT statement:
SELECT left.id, left.attribs FR
Mike Winter wrote:
On Wed, 20 Aug 2003, Rod Taylor wrote:
Ensure your IN list is unique. You might find better times by through
an indexed temp table.
That is what I ended up doing, but it's not a very elegant solution.
MySQL does queries of this type orders of magnitudes faster than Postgres
on
George McQuade wrote:
date tran glamt
08/20/03 1001 3010 -30.00
08/20/03 1001 1030 -300.00
08/20/03 1001 1060 +330.00
08/20/03 1002 ...next transaction
...
and I need to convert to:
date glcr gldb amt
08/20/03 1060 3010 30.00
08/20/03 1060 1030 300.00
in other words, the negative
George McQuade wrote:
Interesting, my C is gone a long time ago. Would the
table function be fairly complex for someone who's
never done one?
I'm tempted by the java option, but initial jdbc speed
tests don't look very promising (the avg file has
1/2 million records).
Well this is a fairly complex
Kumar wrote:
Create table t1 (c1 int, c2 varchar, c3 varchar);
Create or Replace function sel_t1 () returns setof records as '
select c1, c2, c3 from t1; ' Language SQL;
It was fine and created a function. while i execute it as
select sel_t1;
I got the following error.
ERROR: Cannot display
Chris Faulkner wrote:
Is there a function in postgres to return the length of an array field ? I
have seen array_dims(array) but this returns a character value. Ideally, I'd
like something numeric returned.
Afraid not in 7.3.x or earlier. In 7.3.x (but not earlier) you *can* do
this (for a one-dim
Chris Faulkner wrote:
I would like to do it like this in Oracle
select field from table N where [conditions]
and NVL(N.level[1],0) = 0
and NVL(N.level[2],0) = 0
and NVL(N.level[3],0) = 0
and NVL(N.level[4],0) = 0
So if a row only has two elements in the array, but the first two both had
values "0"
Merrall, Graeme wrote:
Am I right in thinking that recursive procedures and procs returning row
sets would allow us to better emulate this behaviour? As anyone looked
at it yet?
See connectby() in contrib/tablefunc. Someone was working on SQL99
recursive queries but it didn't get done for 7.4 -- p
Richard Sydney-Smith wrote:
CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS
int4 AS ' -- search for the position of $2 in $1
declare srcstr alias for $1; searchstr alias for $2;
begin return position(searchstr in srcstr); ' LANGUAGE 'plpgsql'
VOLATILE;
You are missing the "end"
Tom Lane wrote:
Josh Berkus <[EMAIL PROTECTED]> writes:
Also, this will run faster if you do it as a SQL function:
CREATE FUNCTION locate ( text, text ) RETURNS INT AS '
SELECT POSITION($2, $1);
' LANGUAGE SQL IMMUTABLE STRICT;
This is definitely the solution I'd recommend for 7.4 (because 7.4 wou
Thomas Wegner wrote:
SELECT "ID_WINUSER" FROM connectby('"WINUSER"', '"ID_WINUSER"',
'"REF_ID_WINUSER"', 4, 0, '~') AS t("ID_WINUSER" integer)
and get this error:
ERROR: Query-specified return tuple not valid for Connectby: wrong number
of columns
Please see the documentation (README.tablefunc).
Richard Huxton wrote:
So - gain by not re-planning on every call, but maybe lose because your plan
is not so precise.
Of course, any queries you build dynamically and run via EXECUTE will have to
be planned each time.
This question gets even more complex in 7.4, where many simple SQL
functions
Richard Huxton wrote:
On Monday 20 October 2003 18:24, Joe Conway wrote:
This question gets even more complex in 7.4, where many simple SQL
functions will get inlined, and library preloading is available to speed
that first PL/pgSQL call.
What will be the effects of inlining? Does it mean the
Josh Berkus wrote:
Yes, I believe so (well, actually the optimizer). An inlined SQL
function ends up behaving like a macro that expands at run time and is
therefore quite fast -- no function call overhead at all.
... but only in 7.4. In 7.2 and I think in 7.3 this was not implemented.
Yeah, tha
Adam Witney wrote:
The missing values seemed to have been ignored and so the data is being
shifted to the left and so put in the wrong columns.
Am I using this function correctly? What is supposed to happen with missing
values?
Yeah, that's a limitation of the version of crosstab distributed with
David B wrote:
SELECT to_char( created_timestamp, 'DDMMYY' ) AS "joined_on",
r_app.joined_on ; -- HOW do I reference this value?...this does not work
Try either making that first line:
... AS joined_on,
(i.e. without the double quotes) or make the second one:
r_app."joined_on";
(i.
BenLaKnet wrote:
I use postgresql 7.2.3
How can I use connectby ??
Must I install files ? or packages ? or it is recommanded to upgrade
dataserver ?
You need to upgrade. Either install 7.3.4 or wait a few weeks and
install 7.4 when it is released.
Joe
---(end of broadc
Abdul Wahab Dahalan wrote:
If I've a table like below.
kk kjpngkvote
01 02 c 10
01 02 b 5
How do I make a query so that I can get a result
like this?
kk kjpngkvote
01 02c,b 15
create or replace function accum_text(tex
Kumar wrote:
I am migrating an SQL Server 2000 database to Postgres 7.3.4 running
on RH Linux 7.2. While migrating I encounter SQL Server REVERSE( )
function, seems no such functions at Postgres.
Is there a equivalent function available at Postgres? Please shed
some light
How about:
create or repl
Andreas Tille wrote:
test=# select PLpgSQLDepartmentSalaries() ;
This should be:
regression=# select * from PLpgSQLDepartmentSalaries();
departmentid | totalsalary
--+-
1 | 8
2 | 6
(2 rows)
HTH,
Joe
---
Richard Huxton wrote:
On Wednesday 26 November 2003 15:40, Andreas Tille wrote:
I want to write a function of the following type
CREATE FUNCTION test ( )
RETURNS setof MyTable
AS
'SELECT * FROM MyTable WHERE id IN $1'
LANGUAGE 'SQL' ;
Not as you've done it. You c
jasiek wrote:
On Sat, 29 Nov 2003 08:49:24 -0800 (PST), ow wrote
explain select b, c, a
from test
group by b, c, a
having count(*) > 1
Why would you expect this to use an index scan when it needs to read the
entire table? If you read the whole table (or even a significant
fraction of it), a seq
1 - 100 of 156 matches
Mail list logo
