You can have the function return a record but still when you call it you need to pick
only one of its fields :-/
CREATE FUNCTION lala(int4) RETURNS my_table AS 'SELECT * from my_table WHERE pkey= $1'
LANGUAGE 'sql';
Let's say it returns the record: {first_name,last_name,id}={'koko','xaxa',100}
On Tue, 12 Jun 2001, Peter Pilsl wrote:
> I've a problem when selecting values out a table.
>
> manana=# select * from test;
> l1 | l2 | l3
> ++
> 1 | 2 | 3
> 2 | 3 | 4
> 3 | 4 | 5
> 4 | 5 | 6
> | 5 | 6
> (5 rows)
>
> where l1 is NULL in the last line.
> now I do
>
I had asked something simular a week back.
This should help you :-)
http://fts.postgresql.org/db/mw/msg.html?mid=121203
cheers,
thalis
ps did someone play too much with the majordomo? it screwes up the list addresses in
Cc: when I reply to a message
On 12 Jun 2001, Alla wrote:
> Guys;
>
>
You can only define the pri key on table creation. If you miss it there, you can only
add a Unique index (which is pretty much the same I think)
Check http://www.postgresql.org/idocs/index.php?sql-altertable.html
cheers,
thalis
On Tue, 12 Jun 2001, P. Dwayne Miller wrote:
> How do I add a pr
On Tue, 5 Jun 2001, John Moo wrote:
> I have a real stupid (propably) problem :)
> I have two tables, one holding accounts:
>
> CREATE TABLE accounts (
> login varchar(32) primary key,
> password varchar(32),
> name varchar(128),
> email varchar(64));
>
> and one holding posts (to news system):
On Thu, 7 Jun 2001, Lehmeier, Michael wrote:
> Hello
>
> When I SELECT a row with a BOOLEAN in it I get either 't' or 'f'.
> But when I use this same value in the WHERE condition I get an error,
> because PostgreSQL demands either 'true' or 'false'.
>
> Example:
>
> testdb=# create table testt
I was under the impression that vacuum was multi-user safe. It does table locking so
you don't have to worry about concurrency corrupting your data. It just might take
longer (either to vacuum or for a user to get a response)
cheers,
t.
On Wed, 13 Jun 2001, Andy Samuel wrote:
> Hi
>
> Is th
(I think) you'd better check the --enable-cassert option in
http://www.postgresql.org/idocs/index.php?install-procedure.html
cheers,
t.
On Wed, 30 May 2001, rui zhong wrote:
>
> Hi,
>
> I try to use SQL statement 'CREATE ASSERTIION', but it does not work, how to use it?
>Does
> PostgreSQL
I was under the impression that primary index is the same as clustered index i.e. the
order in the index matches the physical order the records are stored on disk thus
making it better when doing sequential accesses.
I assume that this is exactly the use of the CLUSTER command, to actually make
In the manual fro creating aggregate functions
(http://www.postgresql.org/idocs/index.php?sql-createaggregate.html) it reads:
sfunc
The name of the state transition function to be called for each input data value.
This is normally a function of two arguments, the first being of type state
Hippl,
I'm interested in calculating the median of a set of numbers. The algorithm
requires that all values are known in advance (ie stored in an array). So the question
is: how can I store everything first in an array so I can later process it given that
I'd like this to be an aggregat
Taken almost literally from the tutorial example
(http://www.postgresql.org/idocs/index.php?app-ecpg.html) the following code:
EXEC SQL DECLARE my_cursor CURSOR FOR SELECT a,b FROM lala WHERE a= :i;
EXEC SQL FETCH FORWARD NEXT FROM my_cursor INTO :tmpa,:tmpb;
throws the following error in the
>From a previous thread, I remember being said that it is mostly an OS issue. So if
>you make your OS kernel aware of your multiple CPUs, Pg will be just fine.
cheers,
thalis
On Thu, 14 Jun 2001, Rich Bowman wrote:
> Will PostgreSQL take advantage of more than 1 CPU? If so, are there any
> be
On Wed, 20 Jun 2001, Gregory Youngblood wrote:
> I have an ID column which is a bigint, and I have another ID column which was
> created using SERIAL, so it is an integer.
>
> Is it possible to use SERIAL to get a int8 datatype instead int (int4)?
Not as far as I know, but there is talk to act
Give the fullname of the file. Make sure the file is on the server machine and not
your local machine (in case you are forwarding pgaccess to another X terminal)
cheers,
thalis
On 16 Jun 2001, Chakravarthy K Sannedhi wrote:
> Hi,
>
> When I am trying to import a file in my home directory int
Does anyone know what the option "Use declare fetch" on the windows ODBC driver mean?
Is there anything in particular to be fine-tuned in ODBC when transfering large data
sets?
TIA,
thalis
---(end of broadcast)---
TIP 6: Have you searched our lis
On Tue, 19 Jun 2001, Tom Lane wrote:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> > Sure, you create a (static) global variable and reallocate memory for it
> > in each call and free it by the finalizer function.
>
> A static would be a bad idea (consider a query with multiple instances
> of
On Wed, 20 Jun 2001 [EMAIL PROTECTED] wrote:
> Hello,
>
> I have a quick RServ question. We have got it all running and it seems to
> work except, only manually. In other words, it works great as long as we
> use the Replicate command.
>
> So my question is... Is the Replicate command supposed
On Thu, 21 Jun 2001, Tom Lane wrote:
> "Thalis A. Kalfigopoulos" <[EMAIL PROTECTED]> writes:
> > I'm still a bit confused about how to declare the type of the transition state.
> > I have a structure that will hold the current state:
> > struct state {
On Fri, 22 Jun 2001, Alex Pilosov wrote:
> Whoa! Your drive is only capable of 3M/s (640M/213 sec) transfer! Your
> data set is 500K*200 bytes=100M. No surprise your queries take 30 seconds
> to run, since its exactly how much it'd take to transfer 100M of data from
> your drive.
>
> Please read
On Fri, 22 Jun 2001, Edwin Grubbs wrote:
> Unless you have some kind of development environment that requires it, you
> should avoid using inheritance. Besides the fact that I recently asked on
> the list on how to get indexes to work when selecting from all the
> inherited tables at once and sti
I'll take advantage of the fact the Pg is "open source" and that Bruce comments on the
story, so as not to be flamed about this:
http://news.cnet.com/news/0-1003-200-6352301.html?tag=prntfr
Q: What are 1M lawyers at the bottom of the sea?
A: A good start
cheers,
thalis
ps any lawyers in the
On Mon, 25 Jun 2001, Bruce Momjian wrote:
> > On Mon, 25 Jun 2001, Bruce Momjian wrote:
> > > > Is RedHat simply providing PostgreSQL support or are they
> > > > placing developers to work on enhancements/bug fixes as well?
> > >
> > > They are placing developers too. New people. I assume they
On Mon, 25 Jun 2001, Tom Lane wrote:
> "Thalis A. Kalfigopoulos" <[EMAIL PROTECTED]> writes:
> > I noticed that doing a join with the INNER JOIN ON... syntax gives a different
>execution plan (for complex queries at least) than when using the ',' syntax w
On 25 Jun 2001, Trond Eivind [iso-8859-1] Glomsrød wrote:
> "Thalis A. Kalfigopoulos" <[EMAIL PROTECTED]> writes:
>
> > Always a first time for everything bad. Anyway, not wanting to be the
> > pessimist of the bunch, I'll hold my horses and hope that no
On 25 Jun 2001, Trond Eivind [iso-8859-1] Glomsrød wrote:
> "Thalis A. Kalfigopoulos" <[EMAIL PROTECTED]> writes:
>
> > On 25 Jun 2001, Trond Eivind Glomsrød wrote:
> >
> > > "Thalis A. Kalfigopoulos" <[EMAIL PROTECTED]> writes:
&g
EXPLAIN'ing the very simple query:
SELECT * FROM experimentsc WHERE expid=12;
I get the following plan:
NOTICE: QUERY PLAN:
Index Scan using experimentsc_expid_i on experimentsc (cost=0.00..2.01 rows=1
width=44)
EXPLAIN
I have two indeces on the same thing: expid (don't as why :^) One is
It's not in the docs because it has to do with the use you make of your OS. The docs
only mention that shmem is used by Pg so increasing it could benefit you. The rule of
thumb (last week's list archives) is to tell postgres on startup to use shmem
equivalent to approximately 1/4 of you total m
On Tue, 26 Jun 2001, Andrew Snow wrote:
>
> > Yes, but are they going to be collaborating closely with the
> > current Pg core devel team or are they going to work on their
> > own? The concern is regarding the Cnet article about "Redhat
> > forking off eventually with their own pg". Their repre
On Tue, 26 Jun 2001, Itzinger, Oskar wrote:
> In PostgreSQL 7.1.2, I'm experimenting with the following two cases (all
> referred to non-temporary tables exist in the current database):
>
> =
>
> 1.
>
> CREATE TEMPORARY TABLE tmp (
> col1 TEXT,
> col2 TEXT,
> col3 TEXT,
> col4
On Wed, 27 Jun 2001, Edmund von der Burg wrote:
> Hello,
>
> For a project I am working on I needed some way of storing a variable for
> the duration of a session and cooked this up, based on some previous posts
> to this list:
>
>
> create sequence variable_id_seq;
>
> create table variables
This was asked repeatedly the past 2 weeks. With regard to "what is a sane value for
shmmax in the kernel?" Oracle's recommendation is to go for 0.5*physical_memory. So I
gues that 0.25*physical_memory for Pg should be fine.
cheers,
thalis
---(end of broadcast)
On Wed, 27 Jun 2001, Tim Barnard wrote:
>
> ...This is not the same in my book, since I don't care
> to run RHL in any kind of production environment...
>
>
> What is it about RHL that various people wouldn't
> recommend running it in a production envornment?
> I don't have a contrary view, so
On 9 Jul 2001, Andrew Mayo wrote:
> Questions:-
Answers:- online documentation covers 90% of what you would think of asking about pg
> 1. Does it support the full ANSI-92 SQL syntax especially left, right
> outer join functionality. If not, does it even support outer joins?
yes it does
> 2.
> ze_database=> CREATE TABLE my_table (
> ze_database(> p1 integer,
> ze_database(> p2 integer,
> ze_database(> p3 integer,
> ze_database(> p4 integer);
> CREATE
> ze_database=> CREATE FUNCTION my_add(integer,integer,integer,integer)
> ze_database-> RETURNS integer
> ze_database-> AS 'INSERT INTO
UPDATE tablename SET description=(substring(description for position('test' in
description))||'exam'||substring(description from position('test' in
description)+char_length('test')));
Keep in mind that this will only replace the first occurence of the word 'test' in the
description and that d
I assume that there is in fact a difference between doing:
\df+
and:
select oid,proname,prosrc from pg_proc where proname='func_name';
because I have a plpgsql function for a trigger, which I can see with the second
method (the select...) but not with the first method (the \df+ ). Is it not
On Mon, 16 Jul 2001, Bryan Buchanan wrote:
> Hi,
>
> Would anyone know if it's possible to define a rule to do the folowing.
Why use a rule and not a trigger?
> I have two tables:
>
> journal_master (serial #, account, date, debit $, credit $)
> journal_summary (account, date, debit $, credi
I don't recal finding anything similar to MySQL's password() (quote handy if you ask
me). But if you look under the contrib directory of your source tree you'll find a
pgcrypto directory. Compile and install the functions defined in there.
The substitute which worked in my case was:
encode(dige
On Mon, 16 Jul 2001, Radha wrote:
>
>
> Hi,
>
> I need to store some comments/definition for every field in a
> table. Is there a way to give comment for the columns while creating the
> table itself or after that?
>
> I see something like 'Description' when i want to see the list of
> t
$ pg_restore --help
.
.
.
-t, --table[=TABLE] restore this table only
.
.
.
cheers,
thalis
On Wed, 18 Jul 2001, Liz Pelletier wrote:
> If I have a dump of myDB, and I want to restore only two tables from
> this dump, is there a way to do this? Or do I have to restore the entire
> db?
Trying a new 7.1.2 installation when running gmake check I get a failure at the
abstime test. The regression.diffs file contains the following:
*** ./expected/abstime.out Thu May 3 15:00:37 2001
--- ./results/abstime.out Fri Jul 20 11:12:54 2001
***
*** 47,56
I see in postgresql.conf what seems to be the suggested way to go:
#shared_buffers = 2*max_connections # min 16
This seems to imply that every connection is to take 16kb of shmem on average. From my
understanding this depends on the size of the query->involved relations, the sort_mem
size etc.
Another minor issue that has come to my attention is that when I define
shared_buffers=4
I assume that it'll need a shm segment of 4*8192=32768 bytes
And so I set /proc/sys/kernel/shmall and shmmax accordingly only to find out that
postmaster failes to start because it requests a shms
> I started looking at the trigger support in PostgreSQL and found what I
> needed except I'm not sure how to write the "body" of the trigger. In
> Oracle I write PL/SQL but it seems I may have to write C code on the
> PostgreSQL side. Is this true? Is there a PostgreSQL procedural language
> t
On Mon, 14 May 2001, Gregory Wood wrote:
> > Could u just tell me if it is possible to have a Windows Client and the
> Server running on Linux and having Postgres and the two communicate thru
> something like the ODBC.
>
> Yes, although it's preferable to use a native library.
Is ODBC that bad?
I managed to connect to a postgresql db through Access with ODBC. Does anyone know
what purpose the file $PGHOME/etc/odbcinst.ini serves? I removed it and all still
works. Everything it defines is more or less defined on the win side in the
configuration of the odbc driver.
TIA,
thalis
-
On Thu, 24 May 2001, Linh Luong wrote:
> Hi all,
>
> I am running postgres7.1 and php4.0. When I do top while running my
> query via browser I notice that the postmaster process takes up almost
> 100% of the CPU and this is just for one user. What would happen if
> multiple users does the sam
Someone mentioned the OIDs are unique for every row for an entire database cluster. I
also notice that they are not being reused (like a sequence). Will they wrap around
as soon as the 2^32 boundary is hit?
TIA,
thalis
---(end of broadcast)---
I create the following table:
create table lala (id int,people varchar(10)[][]);
I insert as follows:
insert into lala values(1000,'{{"1_1","1_2"},{"2_1","2_2"}}');
Works just fine:
id | people
--+
1000 | {{"1_1","1_2"},{"2_1","2_2"}}
The
On Wed, 30 May 2001, Steve Wolfe wrote:
>
> I know that this topic comes up fairly often, so I tried to search the
> archives, but the search engine doesn't appear to have info on messages
> after 1999, so forgive me for repeating this topic.
>
>Recently, our OID usage has started to ju
Helloppl,
I have a long query that (summerized) looks something like:
SELECT A.a,B.b
FROM A,B
WHERE A.x=B.x AND (A.y=const1 OR A.y=const2 OR A.y=const3);
where the user provides const1,2,3 at runtime. The problem is in creating a function
out of it:
CREATE FUNCTION myfunc(int4,int4,int4) RETUR
I think there is a hefty amount ($$$) involved in getting your results evaluated by
their committee/judges.
You can get a nice graph about TPC-C from:
http://www.greatbridge.com/download/gbpg70wp1.pdf
cheers,
thalis
On 30 May 2001, ak wrote:
> Hi
>
> Has PostgreSQL ever been featured in TPC
I believe Tom mentioned this sometime ago. If you are picking most of the rows then a
seq_scan is preferable to a lookup through the index. In your case you are touching
100% of customer and almost 100% of neicstats, or at least that's what the optimizer
thinks.
Try vacuum_analyzing the tables
You can kill -TERM the postmaster that is executing the particular query.
cheers,
t.
On Fri, 8 Jun 2001, Dennis wrote:
> If I have a database running, and I see that
> there is a postmaster process taking 99.9% cpu.
>
> ie, a very large insert query.
>
> Is there a way to cleanly shut down
then again the same.
But if it is a text file with multiple INSERTs just by themselves, I'd guess killing
the postmaster would live you with a table only half-full.
cheers,
thalis
On Fri, 8 Jun 2001, Thalis A. Kalfigopoulos wrote:
> You can kill -TERM the postmaster that is executi
56 matches
Mail list logo
