nteed.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
2, 'hex');
END;
$$ LANGUAGE plpgsql;
SELECT '\x000b'::bytea AS want_this,
decode('000b','hex')AS or_this1,
decode('\000\013','escape') AS or_this2;
SELECT f_concat_bytea1('\x00', '\x0b&#x
ve, which means it's trying to
treat them as a single record-column with three fields. Hence the error
message.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
k the same options as ssh I daresay you could get it
working.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
(0 rows)
dynacom=# -- THIS IS INSANE
Perhaps just do an EXPLAIN ANALYSE on both of those. If for some reason
one is using the index and the other isn't then it could be down to a
corrupted index. Seems unlikely though.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing
AS id_a, tb.id AS id_b, ta.d, ta.t
FROM ta JOIN tb USING (d,t);
ROLLBACK;
If the fields were named differently you'd need something like:
FROM ta JOIN tb ON (ta.d1, ta.t1) = (tb.d2, tb.t2)
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql
quote any of your values in your Python code - it's
doing it for you. I'm guessing there are other options beside %s for
other data-types (integers,floats,boolean etc).
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
caping apart from single quotes. It's
especially useful for function bodies which tend to contain their own
string literals.
SELECT length($$ab\nde$$);
length
6
None of this should matter from an application however, since its
database access library should do all the
ies do not have any guaranteed order unless you add an ORDER BY
clause. None of them, no matter how simple.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
.
Apart from Vibhor's suggestion (which is the typical way PG does it) you
can also set the auto field to DEFAULT:
INSERT INTO mytable (rowid, other) VALUES (DEFAULT,'a'), (DEFAULT,'b');
This should work on either system.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsq
7;day' FROM d) = 3;
That ends up doing a bitmap index scan for me.
Of course, it's entirely possible an index on year+month returns too
many rows to be useful.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subs
ble1 SET nm = table2.nm
FROM table2
WHERE table1.sn = table2.sn;
SELECT * FROM table1;
ROLLBACK;
Be careful with aliasing the target of the update (table1 in this case).
As another poster has discovered, that counts as another table in your join.
--
Richard Huxton
Archonet Ltd
--
Sent v
Using the real, windowing versions of first/last in 8.4+ will still
require sorting the whole table (AFAICT) so isn't likely to be much
improvement over a self-join here.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your
with an outer query that
compares mycode=prev_code to get a run length.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
uot; and "last value I happened to find".
If you want the earliest/latest timestamp from each day, use min() and
max().
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
trigger?
If you don't expect this to be common, maybe you could fix the
concurrency issue by taking a table-wide lock that locks out
other writers.
Surely SELECT FOR UPDATE on the parents would be sufficient? If there's
no overlap between (currently non-cyclic) graphs being altered
child" isn't in that set.
4. If there is a cycle, raise an error (which will abort the insert or
update)
If you have a "before" trigger, then step 4 could return NULL rather
than raise an error. That would just skip the insert.
Also, step #1 could be done with a CHECK const
es to go through the issues I had building
the lib etc.)
I'd be surprised if there wasn't a -contrib or -extras rpm with the
relevant files. The community rpms should have them if the "official"
Suse ones don't
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql ma
is being executed?
It's also worth checking that the pg_config you are executing is the one
associated with postgresql-8.4.2 - make sure your PATH is set
appropriately. I'm not sure that it would give this error, but I keep
making the mistake of setting my PATH with the three version
You will need to create the function with SECURITY DEFINER permissions,
as a user who can read from the table. Make sure the application cannot
read from the table and has to use the function.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To
return NOTHING.
Is there a way to achieve this?
Not quite the way you suggest. You could build a series of views with
the WHERE conditions built in to them, and grant permissions on those
though.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org
On 19/02/10 10:58, Gordon Ross wrote:
On 19/02/2010 10:36, "Richard Huxton" wrote:
Works here - are you sure you don't have any triggers interfering?
Doh ! *blush*
Yes, I did, to stop anyone tampering with the audit table ;-)
There you go - it worked :-)
--
Richard Hu
of PostgreSQL from the last few years supports "dollar
quoting".
http://www.postgresql.org/docs/8.4/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
That lets you do:
create or replace function ... as $$
...
IF myvar = 'normal-quoting works here' THEN
...
$$ language plpgsql;
--
Ric
(-1,1), (-2,2), (-3,3);
UPDATE audit SET key = extension.number
FROM extension
WHERE audit.record_id = extension.number;
SELECT * FROM audit ORDER BY id;
ROLLBACK;
CREATE TABLE
INSERT 0 3
INSERT 0 3
UPDATE 3
id | record_id | key
+---+-
1 | 1 | 1
2 | 2 | 2
3 |
r, for this case you can just do an update with an extended where
clause:
UPDATE t2 ... WHERE t2f1=NEW.d1 AND NEW.t1f4 > t2.t2f4
No need for the IF.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscript
l.loc_name AS linked_name
FROM
images i2
JOIN location_images li ON i2.img_id = li.img_id
JOIN locations l ON li.loc_id = l.loc_id
...
You could do something clever with inheritance on the joining tables,
but it's better to keep things simple imho.
--
Richard Huxton
Archonet Ltd
--
Sent via pg
to
replace our UNIONs.
You'll want a unique constraint on length_codes.code and you should
really write a custom trigger to make sure none of the
min_length..max_length ranges overlap. In practice, you're probably only
setting this table up once so might not bother.
--
Ri
E plpgsql;
SELECT print_array(ARRAY[ '(1,"abc")'::typ1, '(2,"def")'::typ1 ]);
SELECT print_array(ARRAY[ '(1,"abc")', '(2,"def")' ]::typ1[]);
ROLLBACK;
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
number'::regclass;
>
> plpgsql isn't tremendously good with rowtypes that contain dropped
> columns.
I thought that only applied to columns dropped after the function was
defined. Live and learn.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list
ial_number
>
> That seems to be some bug in Postgres ?
If you look earlier you should see a line that says something like: "set
search_path = rma, ..."
Makes it irritating to cut+paste sections of the dump, but it works just
fine.
--
Richard Huxton
Archonet Ltd
--
Denis BUCHER wrote:
> Richard Huxton a écrit :
>> The other thing you could try is printing out row before returning it:
>> RAISE NOTICE 'row = %', row;
>> RETURN NEXT ROW;
>> It might be you've not got what you were expecting.
>
> Thanks a
. ROLLBACK we can change things until we see
the problem.
The other thing you could try is printing out row before returning it:
RAISE NOTICE 'row = %', row;
RETURN NEXT ROW;
It might be you've not got what you were expecting.
--
Richard Huxton
Archonet Ltd
--
Sent via p
Denis BUCHER wrote:
> Richard Huxton a écrit :
>>> ERREUR: wrong record type supplied in RETURN NEXT
>>> CONTEXTE : PL/pgSQL function "hds_bw_find_sn_live" line 26 at return next
>>>
>>> Does someone maybe knows what it could be ?
>>>
PLACE FUNCTION rma.find_sn_live (varchar(30)) RETURNS SETOF
>> rma.serial_number
This is a different function. Function "hds_bw_find_sn_live" has the
wrong type for its "return next".
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pg
---+---+--
> (0 rows)
You've defined getnote() as returning a single getnote_t value I think
you probably want "RETURNS SETOF getnote_t".
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
t splitting
where the files are stored. That might involve setting up symlinks, I
can't remember if 7.3 supported tablespaces.
If you do put some files on the encrypted filesystem and some on the
unencrypted one make sure you don't start PG until both are available. I
don't kno
two rows.
> But to work with hibernate I need the return NEW statement.
Hmm - not sure I can see a way around this. Maybe someone else who knows
Hibernate can help.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make change
p_subdtls_01" does not exist
I think you missed the word "column" in the error message (easy to do,
you know you are naming tables). You've missed the quotes around the
partition-names so it's trying to find a column on a table that matches.
dateTable := 'coll_fp_su
ext \.
Why not just load the dump file into PostgreSQL? Why are you trying to
copy+paste if you want the entire dump?
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
ould be if I just can type the table name.
There is a variable called "search_path" which controls what schemas are
checked for tables, functions etc. If you have two tables with the same
name but different schemas you'll need to use the .
format though.
SET search_path = public
27;t know how you do that, but it should be in the manual.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
he end of lines when it's
expecting "\n".
The postgresql version is 7.4.17.
Might want to set aside some time to upgrade in the near future - you're
missing four full versions since 2003. At the very least, upgrade to
7.4.25 at your earliest convenience.
--
Richard
Glenn Maynard wrote:
On Thu, Jul 23, 2009 at 2:41 AM, Richard Huxton wrote:
Ah [cue light-bulb effect], I think I understand. Your function isn't in the
database is it? Surely your application knows if it's issuing BEGIN..COMMIT?
I'm writing a Python library call. It has no i
Glenn Maynard wrote:
On Thu, Jul 23, 2009 at 1:31 AM, Richard Huxton wrote:
- Let me use SAVEPOINT outside of a transaction,
You are never outside a transaction. All queries are executed within a
transaction.
"Transaction block", then, if you insist.
I think this is the ro
ter, I'll probably end up dropping out of the ORM
and using some uglier SQL to work around this, but this is so trivial
that it's silly to have to do that. I can't do it within the ORM; it
doesn't have the vocabulary.
The ORM can't control transactions, can't call fu
earliest duplicates
WHERE email=tgt_email AND create_date > tgt_date;
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
, transplant the pointer rather than
actually copy the data?
If they are large-objects (stored via the lo_xxx interface) yes. If not,
no. I take it the large blob of xml is one of the fields that you are
classifying as common?
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql
Rob Sargent wrote:
Richard Huxton wrote:
If your functions are all called at the top-level then indeed it
doesn't matter. At a low level though, telling the planner function F1
costs 1000 times more than F2 is useful.
What scares me about this is that for function such as the one
Rob Sargent wrote:
Richard Huxton wrote:
Rob Sargent wrote:
pg-admin is showing 'COST 100' and 'ROWS 1000' for my explicitly
VOLATILE functions.
Thanks for the pointer back to CREATE FUNCTION docs. That would have
told me that those value are indeed the defaults.
Frank Joerdens wrote:
On Fri, May 22, 2009 at 10:56 AM, Richard Huxton wrote:
[...]
Are you sure it wouldn't be easier to hire a Python guru for a couple of
days and have him/her hack the ORM to make it less, um, "simplistic"?
Not sure. :) Your arguments make eminent sense
Are you sure it wouldn't be easier to hire a Python guru for a couple of
days and have him/her hack the ORM to make it less, um, "simplistic"?
There must be an "assemble references into JOINs" point in the code you
could rationalise this at.
--
Richard Huxton
Archonet
N D.da=2 THEN 1 ELSE 0 END) AS a2,
...
FROM D
) AS b
WHERE tkey =
You might also want to look at the crosstab functions in the tablefunc
contrib module (see appendix F of the manuals).
--
Richard Huxton
Archonet Ltd
-
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make c
A single User Session can span across many days.
SELECT * FROM session WHERE (start_ts,end_ts) OVERLAPS (,
);
> I tried using wildcards in timestamp '07/01/2009 11:%:%" but in vain.
I tries googling / searching archives without any success either.
I'd have thought OVERLAPS
Tarlika Elisabeth Schmitz wrote:
> On Fri, 20 Feb 2009 19:06:48 +
> Richard Huxton wrote:
>> try something like:
>>
>> SELECT t1.d, t1.s, t1.c, count(*)
>> FROM t1
>> LEFT JOIN (
>> SELECT d,s,c FROM t2 WHERE x
>> ) AS t2_true USING (d,
c FROM t2 WHERE x
) AS t2_true USING (d,s,c)
GROUP BY t1.d, t1.s, t1.c;
Warning - not tested
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
ou don't need to do anything:
select extract(epoch from ('2009-02-16 22:15:28.034567-06'::timestamp
with time zone - '2009-02-15 22:15:28.034567-06'::timestamp with time
zone));
date_part
---
86400
(1 row)
HTH
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
index has. Or at least to skip sorting.
Afraid not. Have a search of the archives for discussion on "mvcc
visibility indexes" for lots on this.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
e that returns no rows is using a particular index that the
other queries aren't.
Have you had any crashes / power failures / disk errors recently?
Oh - and what version of PostgreSQL is this?
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
trunc to see what it was choosing between)
And in the next one it guesses it has an interval (because that's what
the other thing is, I'm guessing).
=> SELECT '2008-12-09 18:00:00' + '2 hours'::interval;
ERROR: invalid input syntax for type interval: "2008
t) and then the comparison fails.
That seems reasonable to me - you're unlikely to want to discard
information from an equality test.
The obvious question is - why are you comparing a date to a timestamp in
the first place?
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Tom Lane wrote:
> Richard Huxton <[EMAIL PROTECTED]> writes:
>> Tom Lane wrote:
>>> Richard Huxton <[EMAIL PROTECTED]> writes:
>>>> Anyone got anything more elegant?
>>> Seems to me that no document should have an empty dup_set. If it's no
Tom Lane wrote:
> Richard Huxton <[EMAIL PROTECTED]> writes:
>> Anyone got anything more elegant?
>
> Seems to me that no document should have an empty dup_set. If it's not
> a match to any existing document, then immediately assign a new dup_set
> number to it.
hat you're
trying to do.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
native is to separate duplicated and non-duplicated
documents and UNION them. That's simple enough to see what's happening
but does seem ugly.
Anyone got anything more elegant? I'm happy to alter the duplicates
table so long as it doesn't make it complicated to update.
--
Ri
possible, then 7.3.21 at least.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
straint,
> without loosing the performance ?
Not without seeing the bit of the script causing problems.
How/why are you executing vacuum from your function / multi-command
string. I don't see why you have a multi-command string in a script.
--
Richard Huxton
Archonet Ltd
--
Sent via pg
n multiple cursors (see plpgsql and refcursor in the manuals) and
that's a bit fiddly to use.
HTH
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
s
> that the file is not found.
The uuid-ossp.so file? Is it in the right directory? Is it readable by
the postgres user?
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
don't run Windows routinely, so you'll have to check.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Mario Splivalo wrote:
> Richard Huxton wrote:
>> Mario Splivalo wrote:
>>> I have this issue:
>>>
>>> postgres=# select E'\xc5\x53\x94\x96\x83\x29';
>>> ERROR: invalid byte sequence for encoding "UTF8": 0xc553
>>
>>
Richard Huxton wrote:
> Do you have an index on (id,dt_modified) for manage_followup? Can you
> provide an EXPLAIN ANALYSE for this?
> Hi Richard,
>
> Firstly, thank-you very much for your swift reply. To answer your
> question,
> I had not been using an index on dt_modfied
nough to figure out that it can
It's impossible to say which applies to you without knowing the full query.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
ex on (id,dt_modified) for manage_followup? Can you
provide an EXPLAIN ANALYSE for this?
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
n says that savepoints can only be established inside
> transactions, but does it fail fatal enough so that the procedure getss
> aborted? (more fatal than LOCK does?)
I'm not sure I understand what you mean here.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
0 THEN total ELSE -1 END AS new_total
FROM (
SELECT count(*) AS total FROM test WHERE id=$1
) AS raw_total
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
to store a password in a (secured - make sure this is true!) file.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
>
> If I do this, I will get the error message "You can not change
> region_id other tables are reference to it.
>
> HOW CAN DO THIS? (AUTOMATIC UPDATE OF FOREIGN KEY)
When you define your foreign key mark it "ON UPDATE CASCADE" (there is a
similar option for ON DE
000*
> Why the calculation result has so many decimals ?
Because you've cast 100 to numeric, rather than numeric(10,2) or
whatever. This means you get the full accuracy on the calculation - it
won't ever trim a numeric (that's the whole reason to have the type).
--
R
| time with time zone | time
with time zone, time with time zone
(4 rows)
Also available for other built-in types. Been there ages, used by
aggregate funcs iirc.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Ryan Wallace wrote:
Richard Huxton wrote:
Failing that, where I've had many (a dozen) different sources but want
to search them all I've built a textsearch_blocks table with columns to
identify the source and have triggers that keep it up to date.
Once you've built the tex
u only want the first/last
value of some set. You want all of them.
You could accumulate the values in an array and then sort that with the
final-func that create aggregate supports.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
that, where I've had many (a dozen) different sources but want
to search them all I've built a textsearch_blocks table with columns to
identify the source and have triggers that keep it up to date.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsq
---
25/07/2008 10:21:58.464055 BST
SET timezone='EST';
SELECT clock_timestamp();
clock_timestamp
----
25/07/2008 04:22:19.584367 EST
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to
Giorgio Valoti wrote:
postgres=# insert into f(a,b) values(default, default) returning *;
It seems that you can’t use the same syntax with function calls:
select function(default,default);
gives a syntax error. Is it expected?
Um - there is no default value for a function.
--
Richard
sql, I
guess, but I wonder if there are better options nowadays.
The smallest function I've seen is Merlin Moncure's here:
http://people.planetpostgresql.org/merlin/index.php?/archives/4-fun-with-arrays.html
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsq
CREATE FUNCTION users_at_dotcom(text) RETURNS SETOF users AS $$
SELECT * FROM users WHERE email LIKE '%@' || $1 || '.com';
$$ LANGUAGE SQL;
SELECT * FROM users_at_dotcom('archonet');
uid | uname | email
-+-----+---
1 | richar
FROM metadata;
Does that do it for you? Try the SELECT clauses by themselves to check
if they're doing the right thing.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
aticfields VALUES (91);
INSERT INTO staticfields VALUES (86);
INSERT INTO metadata (value, field, mydate)
SELECT value, f, mydate
FROM metadata, staticfields;
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscrip
eem to take a long time.
Do you have any foreign keys referencing used_diary? Do they have the
correct indexes on the referencing tables?
Any on-update triggers?
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your
w)
=> SELECT extract(month from justify_days('1 week'::interval));
date_part
---
0
(1 row)
If you just have a number (e.g. 14) then you can just do something like:
=> SELECT round(14 * 7 / 30);
round
---
3
(1 row)
--
Richard Huxton
Archonet Ltd
--
Se
8,25)' <@ polygon '((-3,10),(8,18),(-3,30),(-10,20))';
SELECT polygon '((-3,10),(8,18),(-3,30),(-10,20))' @> point '(-8,25)';
I'm running 8.3, but it seems to be in 8.2 too.
Try \do '<@' from psql to see what operators are available.
--
foo_id/bar_id then you'll want the foreign-key of course.
The one thing I would do is change the names of foo_id, bar_id since
they're not identifiers by themselves.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make chan
've forgotten what your search_path is set to then that
can cause confusion.
Not a problem I see much of since I very rarely change my search_path. I
refer to schema.table by preference.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.
.org/projects/mysql2pgsql/
http://pgfoundry.org/projects/my2postgres/
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
pose:
http://rubyforge.org/projects/ruby-pg/
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
TEXT) IS
$$Pattern-matching permission revoker. See grant_all(...) for details.
$$;
CREATE OR REPLACE FUNCTION util.drop_all_roles(pattern name) RETURNS
TEXT AS $$
DECLARE
rRECORD;
sql TEXT;
res TEXT;
BEGIN
res := 'Dropped: ';
FOR r IN SELECT
[EMAIL PROTECTED] wrote:
Thanks for the help again, Richard. I will look into upgrading to 8.3
as I think that will be the best solution.
I don't think you'll regret it. There are some excellent improvements in 8.3
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing l
ng PostgreSQL
8.2.3.1. I don't know if that matters too much.
Ah, sorry - that was an 8.3-only cast.
I think your only real option will be to build your query as a string
and use EXECUTE to get the bit-varying you want in your function.
--
Richard Huxton
Archonet Ltd
--
Sent via pgs
;s not happy - if I create an empty table to go with
it, it runs here. If you replace them with RETURN QUERY SELECT '' does
that make the error go away?
Oh, and are you sure you mean IMMUTABLE? That's only true if hg18.genome
is a static table.
--
Richard Huxton
Archonet Ltd
achieve this?
Try casting from a string:
SELECT ('1' || repeat('0', n-1))::bit varying;
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql
1 - 100 of 859 matches
Mail list logo
