Did you see:
http://www.postgresql.org/users-lounge/docs/7.0/user/functions2872.htm
On Mon, 5 Mar 2001, Josh Berkus wrote:
> I can't find any documentation for what masks to use with the function
> TO_CHAR(INTERVAL, mask). Is there a TO_CHAR(INTERVAL)? If so, what
> masks are there? If
Grant,
The only way I know to enumarate arrays is procedural. This is a good
reason to stay away from arrays except in buffer and temporary tables
(aside from the fact that array columns violate the relational principle
of atomicity).
The following sample procedure, paraphrased
Bruce, Tom, et. al.,
I can't find any documentation for what masks to use with the function
TO_CHAR(INTERVAL, mask). Is there a TO_CHAR(INTERVAL)? If so, what
masks are there? If not, how would you suggest I convert an interval
value for user-friendly display?
Justin Long <[EMAIL PROTECTED]> writes:
> Ok, now I have another question... it doesn't seem to be accessing the index.
> explain select k.kbid,k.title from knowledge k , kbwords w0 , kbwords w1
> WHERE ((k.kbid=w0.kbid and w0.wordid=42743) AND (k.kbid=w1.kbid and
> w1.wordid=85369))
> NOTICE:
Boulat Khakimov <[EMAIL PROTECTED]> writes:
> Here is a nifty query I came up with
> that provides a detailed information on any row of any table.
> Something that is build into mySQL (DESC tablename fieldname)
> but not into PG.
Er, what's wrong with psql's "\d table" ?
Hi,
Here is a nifty query I came up with
that provides a detailed information on any row of any table.
Something that is build into mySQL (DESC tablename fieldname)
but not into PG.
SELECT a.attname AS Field,
c.typname as Type,
a.atttypmod-4 AS Size
FROM pg_attribute a,
pg
On Tue, 06 Mar 2001 06:55, [EMAIL PROTECTED] wrote:
> Hi all!
> I wonder how in PG7.0.2 I can count the number of weeks from a date
> field that I have in a table, lets say:
>
> table trial has in its fields start_date as sysdate(), and now I want to
> do a select that will give me the number of w
if you are a user with privalege can create table
psql -e dbname I want to build my tables by placing all the sql
> statements in a file. What is the correct way to use
> this file with psql?
>
> Example: My text file has this in it:
>
> CREATE TABLE table1 (
>table1_id serial,
>field1
Tom Lane wrote:
>
> Boulat Khakimov <[EMAIL PROTECTED]> writes:
> > ERROR: Can't find function encrypt in file /[full path here]/encrypt.so
> > Why do I get this error
>
> Offhand I see nothing wrong with your procedure. Try running 'nm' on
> the .so file to see what symbols it says the .s
Does vacuuming and analyzing a database affect the users if they are
currently inserting/deleting rows from a table on the database? How does
it work exactly...
Thanks.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unr
Boulat Khakimov <[EMAIL PROTECTED]> writes:
> ERROR: Can't find function encrypt in file /[full path here]/encrypt.so
> Why do I get this error
Offhand I see nothing wrong with your procedure. Try running 'nm' on
the .so file to see what symbols it says the .so defines.
What platform is t
On Mon, Mar 05, 2001 at 04:59:47PM -0500, Justin Long allegedly wrote:
> Ok, now I have another question... it doesn't seem to be accessing the index.
>
> explain select k.kbid,k.title from knowledge k , kbwords w0 , kbwords w1
> WHERE ((k.kbid=w0.kbid and w0.wordid=42743) AND (k.kbid=w1.kbid an
On Mon, Mar 05, 2001 at 08:40:53AM -0800, clayton cottingham allegedly wrote:
> Boulat Khakimov wrote:
> >
> > David Olbersen wrote:
> > >
> > > On Sun, 4 Mar 2001, Boulat Khakimov wrote:
> > >
> > > ->How do I encrypt/decrypt something in PG?
> > >
> > > Perhaps it'd be better to one-way encrypt
Did you run VACUUM ANALYZE after running CLUSTER?
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Mon, 5 Mar 2001, Justin Long wrote:
> Ok, now I have another question... it doesn't seem to be accessing the index.
>
> explain select k.kbid,k.title from
How can I obtain a count (number) of elements in a text array? Thanks.
test=# \d array
Table "array"
Attribute | Type |Modifier
--+-+
id | integer | not null def
On Mon, Mar 05, 2001 at 04:45:47PM -0500, Bruce Momjian allegedly wrote:
> Yes.
>
> > On Mon, Mar 05, 2001 at 04:07:57PM -0500, Bruce Momjian allegedly wrote:
> > > Have you tried VACUUM ANALYZE and CLUSTER?
> >
> > I assume CLUSTER still drops all indexes except the one you're clustering
> > on
Ok, now I have another question... it doesn't seem to be accessing the
index.
explain select k.kbid,k.title from knowledge k , kbwords w0 , kbwords w1
WHERE ((k.kbid=w0.kbid and w0.wordid=42743) AND (k.kbid=w1.kbid and
w1.wordid=85369))
NOTICE: QUERY PLAN:
Merge Join (cost=32339.30..35496.97
On Mon, Mar 05, 2001 at 11:08:40AM -0800, David Olbersen allegedly wrote:
> On Mon, 5 Mar 2001, Jeff S. wrote:
>
> ->I want to be able to use the file to create my table.
> ->I've tried psql -d databasename -e < filename.txt
> ->but that doesn't work.
>
> You're making it too dificult :-)
> 'psq
Yes, it drops indexes, much to my chagrin, as I just realized ...
including SERIALs...
Justin
At 04:45 PM 3/5/2001 -0500, you wrote:
Yes.
> On Mon, Mar 05, 2001 at 04:07:57PM -0500, Bruce Momjian allegedly
wrote:
> > Have you tried VACUUM ANALYZE and CLUSTER?
>
> I assume CLUSTER still drops a
Does that mean that if you have 3 indexes on a table and you cluster one,
it deletes the other 2?
At 04:45 PM 3/5/2001 -0500, you wrote:
Yes.
> On Mon, Mar 05, 2001 at 04:07:57PM -0500, Bruce Momjian allegedly
wrote:
> > Have you tried VACUUM ANALYZE and CLUSTER?
>
> I assume CLUSTER still drop
Hi,
Im writing a C function for PG to do one way encryption using crypt.
Here is the source code
#include
#include
#include "postgres.h"
#include "utils/builtins.h"
text *encrypt(text *string){
text *ret;
int m;
if ((string == (text *) NULL
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > But random returns a random value from 0-1, right? How does that work
> > in ORDER BY?
>
> What's the problem? Each row gets a different random value, then we
> sort.
Oh, I see. Nifty. I am used to seeing a column name or number in ORDER
BY. W
Yes.
> On Mon, Mar 05, 2001 at 04:07:57PM -0500, Bruce Momjian allegedly wrote:
> > Have you tried VACUUM ANALYZE and CLUSTER?
>
> I assume CLUSTER still drops all indexes except the one you're clustering
> on?
>
> Mathijs
> --
> It's not that perl programmers are idiots, it's that the languag
On Mon, Mar 05, 2001 at 04:07:57PM -0500, Bruce Momjian allegedly wrote:
> Have you tried VACUUM ANALYZE and CLUSTER?
I assume CLUSTER still drops all indexes except the one you're clustering
on?
Mathijs
--
It's not that perl programmers are idiots, it's that the language
rewards idiotic behavi
Bruce Momjian <[EMAIL PROTECTED]> writes:
> But random returns a random value from 0-1, right? How does that work
> in ORDER BY?
What's the problem? Each row gets a different random value, then we
sort.
regards, tom lane
---(end of broadcast)---
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> >> Jelle Ouwerkerk <[EMAIL PROTECTED]> writes:
> > Also, is there a way to randomize the order of a result set?
> >>
> >> There's always
> >> SELECT * FROM foo ORDER BY random();
>
> > However:
> > test=> select * from pg_class order by random();
On Mon, 5 Mar 2001, Bruce Momjian wrote:
> > Jelle Ouwerkerk <[EMAIL PROTECTED]> writes:
> > > Also, is there a way to randomize the order of a result set?
> >
> > There's always
> > SELECT * FROM foo ORDER BY random();
> >
>
> How does that work?
>
> test=> select random();
>
Bruce Momjian <[EMAIL PROTECTED]> writes:
>> Jelle Ouwerkerk <[EMAIL PROTECTED]> writes:
> Also, is there a way to randomize the order of a result set?
>>
>> There's always
>> SELECT * FROM foo ORDER BY random();
> However:
> test=> select * from pg_class order by random();
> does return s
Have you tried VACUUM ANALYZE and CLUSTER?
> Any suggestions welcome!
>
> Here is my query:
>
> select k.*, c.category from knowledge k, kb_categories c , kbwords w0 ,
> kbwords w1 WHERE k.catid=c.catid AND ((k.kbid=w0.kbid and w0.wordid=42743)
> AND (k.kbid=w1.kbid and w1.wordid=85369)) ORD
> Jelle Ouwerkerk <[EMAIL PROTECTED]> writes:
> > Also, is there a way to randomize the order of a result set?
>
> There's always
> SELECT * FROM foo ORDER BY random();
>
How does that work?
test=> select random();
random
---
Can someone comment on this? Is it being merged into the main docs?
> Hi all,
>
> I finished the beta version of my PL/SQL-to-PL/PgSQL-HOWTO last night
> and put it in http://www.brasileiro.net/roberto/howto .
> It explains basic differences between Oracle's PL/SQL and PG's
>
Any suggestions welcome!
Here is my query:
select k.*, c.category from knowledge k, kb_categories c , kbwords w0 ,
kbwords w1 WHERE k.catid=c.catid AND ((k.kbid=w0.kbid and
w0.wordid=42743) AND (k.kbid=w1.kbid and w1.wordid=85369)) ORDER BY
k.kbid DESC LIMIT 25;
Now for the details
knowledge k
Jelle Ouwerkerk <[EMAIL PROTECTED]> writes:
> Also, is there a way to randomize the order of a result set?
There's always
SELECT * FROM foo ORDER BY random();
regards, tom lane
---(end of broadcast)---
TIP 3: if pos
Here is the senario...
I have a table defined as
create table details (
field1
field2
.
.
.
);
and a function:
create function get_details(int4) returns details as '
declare
ret details%ROWTYPE;
site_recrecord;
cntct contacts%ROWTYPE;
begin
select
Hi
What would be the best way to select a random row from a result set?
Possibilities:
1) o get the total number of rows using count()
o generate a random number between 1 and the total
o select the n'th row using OFFSET
2) o get the total number of rows using count()
o generate a ran
On Mon, 5 Mar 2001, Jeff S. wrote:
->I want to be able to use the file to create my table.
->I've tried psql -d databasename -e < filename.txt
->but that doesn't work.
You're making it too dificult :-)
'psql -d databasename < filename.txt' should work just fine
-- Dave
---
"Jeff S." wrote:
>
> I want to build my tables by placing all the sql
> statements in a file. What is the correct way to use
> this file with psql?
>
> Example: My text file has this in it:
>
> CREATE TABLE table1 (
>table1_id serial,
>field1 char(5),
>PRIMARY KEY (table1_id)
> );
I want to build my tables by placing all the sql
statements in a file. What is the correct way to use
this file with psql?
Example: My text file has this in it:
CREATE TABLE table1 (
table1_id serial,
field1 char(5),
PRIMARY KEY (table1_id)
);
I want to be able to use the file to crea
Hi all!
I wonder how in PG7.0.2 I can count the number of weeks from a date field that
I have in a table, lets say:
table trial has in its fields start_date as sysdate(), and now I want to do a
select that will give me the number of weeks from now [sysdate()] to the
start_date.
Thanks for the he
Unfortunately, in plsql
you only can do DML(select/update/insert) instead of DDL(create/grant..).
i.e. you cannot create a table in plsql.
Jie LIANG
St. Bernard Software
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.stbernard.com
www.ipinc
Unfortunately, PL/PGSQL cannot pass table name.
I have same problem, only thing I can do is pass an integer, then use IF
.. THEN .. ELSE .. END IF;
Jie LIANG
St. Bernard Software
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.stbernard.com
Boulat Khakimov wrote:
>
> David Olbersen wrote:
> >
> > On Sun, 4 Mar 2001, Boulat Khakimov wrote:
> >
> > ->How do I encrypt/decrypt something in PG?
> >
> > Perhaps it'd be better to one-way encrypt something? Granted I don't know the
> > details of your project, but allowing a way to "decrypt
Salvador Mainé wrote:
> Hello:
>
> I'm trying to define a function that, given a date, returns its month.
> The definition is as follows:
>
> CREATE function anyo_hidro (date) returns int AS '
>BEGIN
>RETURN date_part("month",$1);
>END;
> ' LANGUAGE 'plpgsql';
>
> But when
David Olbersen wrote:
>
> On Sun, 4 Mar 2001, Boulat Khakimov wrote:
>
> ->How do I encrypt/decrypt something in PG?
>
> Perhaps it'd be better to one-way encrypt something? Granted I don't know the
> details of your project, but allowing a way to "decrypt" something is rather
> insecure.
>
>
44 matches
Mail list logo
