ow wrote:
My impression was that the index "I_bca" covers the query, hence there should
not be a need to go to the table itself. Why would it?
Postgres always has to go to the table. The ability to read data
directly from indexes (ala other RDBMSs) has been discussed, but not
implemented. IIRC it
Michael Fuhr wrote:
I wrote a haversine() function that uses the Haversine Formula to
calculate the great circle distance between two points on a sphere
(assuming the earth is a perfect sphere is accurate enough for my uses).
Here's a web site with related info:
http://www.census.gov/cgi-bin/geo/gi
Chris Bowlby wrote:
select get_account_info('test.com');
I get this error:
ERROR: set-valued function called in context that cannot accept a set
This is the "classic" SRF error -- you need to use an SRF like a
relation in the FROM clause, so do this instead:
select * FROM get_account_info('
Kumar wrote:
select * from .fn_email(1) as (email_folder_id int4,email_folder_name
varchar,descrip varchar,msgcount int8,unreadcount int8,size int8);
Is it possible to fetch only one column (the 'msgcount') from the
function. Because I am interested in SUM(msgcount). Please shed some
light.
What
Tom Lane wrote:
Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes:
can anyone explain why
SELECT array_lower(array_prepend(0, ARRAY[1,2,3]), 1);
returns 0 not 1
Because array_prepend keeps the subscripts of the existing array
elements the same. This was discussed during development of the
code, but I
Tom Lane wrote:
The other point about pg_dump failing to correctly restore arrays with
nondefault lower bounds is a good one, though. We need to think about
how to fix that.
I'll put some thought into it, but note that it is hardly a new issue --
it's been possible to create an array with < 1 low
Tom Lane wrote:
Of course; I suppose this bug goes back to Berkeley days. We just
hadn't recognized it before (or at least I hadn't).
Neither had I. But the changes in 7.4 probably make it more likely
people will bump into this as a problem.
Without looking to confirm, I believe SQL99 defines an
Chris Travers wrote:
This is a complex issue, and i am tryign to figure out how to use regular
expressions to resolve this issue. I need to retrieve the first N lines of
a text field. N would be assigned using a parameterized query, if possible.
How 'bout something like this:
CREATE OR REPLACE F
Bruce Momjian wrote:
Is this a TODO?
Probably -- something like:
Modify array literal representation to handle array index lower bound
of other than one
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http:/
Tom Lane wrote:
"David" <[EMAIL PROTECTED]> writes:
INSERT INTO species (location) VALUES ('{((432,89), (45,87), (89,87)),
((54,78), (34,98))};
I think you'd need to double-quote each polygon within the array
literal.
'{"((432,89), (45,87), (89,87))", "..."}'
The array parser doesn't think parens
Philippe Lang wrote:
I need to do something similar to a cross tabulation, but without any
aggregation.
See the crosstab() function found in contrib/tablefunc
Joe
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister
Rodrigo Sakai wrote:
I insist in my question, is there a way to compile the
plpgsql codes or something like that
no
think about writting this postgres functions in C??
yes
Joe
---(end of broadcast)---
TIP 9: the planner will ignore your desire
Richard Huxton wrote:
That's not quite the same though, because it means I need to split
ABCAA..ABDBB into ABCAA..ABCZZ and ABDAA..ABDZZ but it's close enough unless
someone is feeling clever this evening.
Would (a series of) partial indexes help?
Joe
---(end of broadca
Greg Sabino Mullane wrote:
How to find the last sunday/mon/sat of any given month.
There is probably a smoother way to do it, but here is a
quick little function to do what you ask. Feed it a date
and a number, where 0 is Sunday, 1 is Monday, etc.
oops...forget my last reply...I was a bit too
Yasir Malik wrote:
[attempts to use a composite type as a field data type of another
composite type]
When I enter that into the command prompt, I the following message:
ERROR: Attribute "street" has composite type street_type
Why is it giving me error message for something I know is true? Also,
[EMAIL PROTECTED] wrote:
When I run the function below I recieve an error message saying that
column definitions need to be specified for a function returing a
type RECORD.
I was under the impression that the FOR row IN SELECT... loop would
assign a column structure to the RECORD type. Am I wrong a
Tom Lane wrote:
Stephan Szabo <[EMAIL PROTECTED]> writes:
On Tue, 2 Mar 2004, Terence Kearns wrote:
Well I haven't yet done anything because I couldn't get anything to
compile which returned SETOF RECORD..
As a starting point, SETOF "RECORD" is different from SETOF RECORD given
PostgreSQL's fold c
Achilleus Mantzios wrote:
is there a way to encode a bytea in such a way that the resulting
text stream be readily available (\\ escaped for unprintable chars) for
usage in an insert statement?
None of base64,hex,escape options in encode() seem to produce
anything close.
This is meant to be used
Josh Berkus wrote:
v_vals TEXT[];
n_vals TEXT[];
try:
v_vals TEXT[] := ''{}'';
n_vals TEXT[] := ''{}'';
You have to initialize the array to something non-null, even if that be
an empty array (note that there is a difference). When trying to append
an element to a NULL valued array, you wind
Josh Berkus wrote:
BTW, did you get my e-mail to Hackers about ARRAY[] IS NULL?
I saw it, but I've been too swamped to really read it. I'll try to carve
out some time this afternoon.
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensi
Josh Berkus wrote:
6) SQL-99 Distinct Types
7) SQL-99 Structured Types
9) SQL-99 Collection Types
10) SQL-99 Typed tables and views
My answers:
6), 7) Not sure what these are.
Here's the section in SQL99:
4.8 User-defined types
A user-defined type is a schema object, identified by a
Josh Berkus wrote:
4.16.2 Referenceable tables, subtables, and supertables
A table BT whose row type is derived from a structured type ST is
called a typed table. Only a base table or a view can be a typed
table. A typed table has columns corresponding, in name and
decla
Bruce Momjian wrote:
Martin Marques wrote:
Also, for some reason I can't get to sqlstandards.org. Is there any other
place where I can get the SQL200X docs?
Not sure.
See:
http://www.wiscorp.com/sql/sql_2003_standard.zip
Joe
---(end of broadcast)---
Dennis wrote:
I am trying to return an array from a function and don't seem to be
having luck. The function seems to work fine, but if I do assignment to
an array variable, I get null in the array elements
DECLARE
results varchar[];
tmpv varchar;
BEGIN
-- now call func that returns varchar
Dennis wrote:
pg 7.4.1
I should have listed the source for the function. Here is a simplified
parseString function and the foo that calls it.
dennis=# create or replace function parseString (varchar, varchar)
dennis-# RETURNS varchar[] AS '
dennis'# DECLARE
dennis'#
Greg Sabino Mullane wrote:
How to find the last sunday/mon/sat of any given month.
There is probably a smoother way to do it, but here is a
quick little function to do what you ask. Feed it a date
and a number, where 0 is Sunday, 1 is Monday, etc.
How about this:
regression=# select date_trun
Torsten Lange wrote:
Hello,
I have a table with measurement values and columns like this:
analyses(id, sample_id, parameter[temperatur...], value, unit[?C...], error)
With PL/PgSQL at the end I want try to perform a pivot-like arrangement of
these data:
sample_i
Markus Bertheau wrote:
is the empty array representable in PostgreSQL, and is it
distinguishable from NULL?
Yes, and yes.
regression=# select '{}'::int[];
int4
--
{}
(1 row)
regression=# select NULL::int[];
int4
--
(1 row)
Since NULL array elements are not currently supported, attempting
Markus Bertheau wrote:
How do I specify an empty array with the standard syntax?
Actually, up until the 7.4 release, the array literal syntax was the
*only* syntax (i.e. '{...}'). The newer array constructor expression
(i.e. ARRAY[...]) does not yet support creating empty arrays -- there
are som
Markus Bertheau wrote:
How do I specify an empty array with the standard syntax?
Actually, up until the 7.4 release, the array literal syntax was the
*only* syntax (i.e. '{...}'). The newer array constructor expression
(i.e. ARRAY[...]) does not yet support creating empty arrays -- there
are som
Devin Whalen wrote:
First line:
my @active_tables=split(/,/,$tables);
Is there anyway to split a variable like the perl split above?
I'm no perl guru, but in 7.4 I believe this does what you're looking for:
regression=# select string_to_array('1,2,3',',');
string_to_array
-
{1,2,3
Caleb Simonyi-Gindele wrote:
My problem is that the rows are populated from right to left irrespective of
the column. So the $561.99 in vet1 actually belongs to vet2. There are never
any blank cells in the middle or start of a row - they always come at the
end. How do I get the amount for that date
Philippe Lang wrote:
But the same query with a parameter returns an error:
select id, usr, code, line1, line2 from tbl, get_lines(code);
--> ERROR: function expression in FROM may not refer to other relations
of same query level
This is as expected and required -- you cannot refer to other FROM
Theo Galanakis wrote:
How do I explicidly create a cross-database reference?
Mind you these databases are on the same server.
In MSSQL you could do that through enterprise manager.
Any help would be appreciated.
You cannot do cross database references per se, but you can use schemas,
which roughly
Josh Berkus wrote:
I'm seeing this bizarre, unreproducable error in my logs:
[2] Wrong datatype for second argument in call to in_array
SQL: SELECT sf_event_decendants(66645,111)
I also checked for in_array and it's not a visible built-in function. Is this
maybe a PostgreSQL bug? Version is 7
Bruno Wolff III wrote:
On Sat, Aug 07, 2004 at 09:33:08 +0530,
Kenneth Gonsalves <[EMAIL PROTECTED]> wrote:
any reason why the default reply-to on this list should not be set to the
list? I keep replying to postings only to find later that the reply goes to
the OP and not to the list. reply-all
Josh Berkus wrote:
[2] Wrong datatype for second argument in call to in_array
SQL: SELECT sf_event_decendants(66645,111)
Are you sure this message isn't coming from some PHP middleware, e.g.
peardb or something. See:
http://us2.php.net/manual/en/function.in-array.php
Joe
---
Josh Berkus wrote:
The Problem: for each "case" there are from zero to eight "timekeepers"
authorized to work on the "case", out of a pool of 150 "timekeepers". This
data is stored vertically:
authorized_timekeepers:
case_id | timekeeper_id
213447 | 047
132113 | 021
132113 | 115
132
Josh Berkus wrote:
This is pretty much exactly how contrib/tablefunc's crosstab (non-hashed
version; crosstab(sourcesql, ncols)) works. If you really need it to be
portable, though, application layer procedural code is likely to be the
easiest and fastest way to go. crosstab just wraps the procedur
Markus Bertheau wrote:
Is there a reason the array_in parser accepts additional closing braces
at the end?
oocms=# SELECT '{}}'::text[];
text
--
{}
(1 ÑÑ)
Hmmm, I was *about* to say that this is fixed in cvs (and indeed, the
array_in parser is significantly tightened up compared to previ
Greg Stark wrote:
Just as a side comment, one trick I found very helpful in my mail filters is
to treat any message with one of my message-ids in the references as a
personal message as far as mail notifications. This way I get notifications
for any message on a thread following a post of my own.
I
Sergio Fantinel wrote:
I found how to use, inside a PL/pgSQL function, a two-dimensions array
(matrix).
There is a limitation: the number of the 'columns' of the matrix is
fixed at declaration time (in DECLARE section) and you need to manually
initialize all the elements in the first 'row' of th
Muhyiddin A.M Hayat wrote:
How to create Calendar using Function/View.
For example i would like to display date 2004-12-01 to 2004-12-20.
date
--
2004-12-01
2004-12-02
2004-12-03
2004-12-04
2004-12-05
..
..
2004-12-20
-- Use in Postgres 7.4.x and earlie
[EMAIL PROTECTED] wrote:
SELECT * from dblink('host=192.168.0.50 dbname=AK1 user=akteam','Select
userid from user_reg') as t (userid integer);
I am getting an error as "ERROR: function dblink("unknown", "unknown")
does not exist"
Have you installed the dblink functions into your database? See
REA
Moran.Michael wrote:
My initial attack plan was to do the following:
1. Call decrypt() with the old-passphrase to decrypt each table's existing
data.
2. Temporarily store the decrypted data in temp tables.
3. Delete all rows of encrypted data from the original tables -- thereby
clearing the table
Markus Bertheau wrote:
why does SELECT ARRAY(SELECT 1 WHERE FALSE) return NULL instead of
ARRAY[] resp. '{}'?
Why would you expect an empty array instead of a NULL? NULL is what
you'd get for other data types -- for example:
regression=# SELECT (SELECT 1 WHERE FALSE) IS NULL;
?column?
Tom Lane wrote:
I think he's got a good point, actually. We document the ARRAY-with-
parens-around-a-SELECT syntax as
The resulting one-dimensional array will have an element for
each row in the subquery result, with an element type matching
that of the subquery's output
Bruce Momjian wrote:
Joe Conway wrote:
Any thoughts on how this should be handled for an empty 1D array?
No one responed to this email, so I will try. Is this the one
dimmentional array you were talking about?
test=> select array_dims('{}'::integer[]);
Tom Lane wrote:
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= <[EMAIL PROTECTED]> writes:
By analogy, array_upper('{}'::TEXT[], 1) should return 0 instead of
NULL.
No, that doesn't follow ... we've traditionally considered '{}' to
denote a zero-dimensional array. A 1-D array of no elements is
'[1:0]
[EMAIL PROTECTED] wrote:
I'm trying to do a simple query and I'm not sure how to get it to work:
SELECT SUM(x0 + y0 + z0) / SUM(x2 + y2) AS A1, SUM(x1 + y1 + z1) / SUM(x3 + y3)
AS A2
FROM test
Problems:
1. All variables are integers. When it does the division, it returns an
integer, but I wan
rec.table_name;
EXECUTE sql INTO startval;
IF startval IS NOT NULL THEN
sql := 'ALTER SEQUENCE ' || seqname || ' RESTART WITH ' ||
startval;
EXECUTE sql;
RAISE NOTICE '%', sql;
010-09-03
2 | 2010-09-04
2 | 2010-09-05
2 | 2010-09-06
2 | 2010-09-07
2 | 2010-09-08
(10 rows)
HTH,
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
signature.asc
Description: OpenPGP digital signature
o idea.
The error is because you are selecting from a set returning function in
the target list rather than the from clause. It should be more like:
SELECT * FROM crosstab(text, text) AS ( col1_name col1_type [, colN_name
> colN_type]* )
HTH,
Joe
--
Joe Conway
credativ LLC: http://www.creda
T E Schmitz wrote:
Alvaro Herrera wrote:
T E Schmitz wrote:
I have written a shell script to export data:
psql -A -t -U $DBUSER -d $DB -c "$QUERY" -F ' '
Currently, I am using spaces as field separator but what I really
want is tabs.
How can I specify a TAB character with the -F option?
Tom Lane wrote:
Richard Jones <[EMAIL PROTECTED]> writes:
I've been profiling a PG database / mix of applications and found that
one statement which takes a very long time to execute is:
PG 8.2 does better with long IN-lists ... although if the list is so
long as to be fetching a significant f
Marko Kreen wrote:
On 6/5/07, Brian Mathis <[EMAIL PROTECTED]> wrote:
pgcrypto also supports md5, so I'm not sure what you're referring to
here.
digest(psw, 'md5') vs. crypt(psw, gen_salt('md5'))
As I already mentioned, *salting* before you hash is a very
important step. I'm not sure if you
101 - 156 of 156 matches
Mail list logo
