Quoth pavel.steh...@gmail.com (Pavel Stehule):
> Dne 25.3.2013 23:51 "Ben Morrow" napsal(a):
> >
> > I would use a view for this:
> >
> > create view vale_any as
> > select 'P'::text "type", v.adiant, v.desc_per, v.cod
> &
er, v.cod
from valepag v
union all
select 'R', v.adiant, v.desc_per, v.cod
from valerec v;
then
for rSql in
select a.adiant, a.desc_per
from vale_any a
where a.type = cTip and a.cod = 2
loop
You need to cast the constant in the view definitio
of the untrusted
> languages. or in C you could call zlib.
You can call Compress::Zlib or Archive::Zip (depending on which sort of
'zip' you mean) from (trusted) PL/Perl, provided you load the modules
from plperl.on_init.
Ben
--
Sent via pgsql-sql mailing list (pgsql-sql@po
WHERE (field2 = value_1 OR field2 = value_2 OR .OR field2 = value_n)
>
> I build such a query using a programming language and, after that, I
> execute it. Is this a good approach to build such a query?
You can use IN for this:
UPDATE table1
SET field1 = "some value&qu
Hi,
I'm trying to write a function that will take a name as a text value,
and return an XML element with that name as name, like so:
create function xpercent(nam text, val int) returns xml as $$
begin
return ( select xmlelement(name nam, concat(val::text, '%')) );
end;
$$ language
Quoth lists-pg...@useunix.net (Wayne Cuddy):
> On Thu, Feb 28, 2013 at 06:02:05PM +0000, Ben Morrow wrote:
> >
> > (If you wanted to you could instead rename the table, and use rules on
> > the view to transform DELETE to UPDATE SET state = 'deleted' and co
it GRANT on the view? Make sure
you've read section 37.4 'Rules and Privileges' in the documentation,
since it explains the ways in which this sort of information hiding is
not ironclad.
Ben
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
ate1).
>
> So at a guess you are actually creating the new database from template1.
Also, SQL names are folded to lowercase unless they are quoted, so
TEMPLATE = Default
would refer to a database called "default" (if it weren't special syntax
for template1), but
TEMPL
x27;t use base64.) If you were going to do that
you would need to consider the possibility of an attacker arranging a
hash collision: I don't know where you're labels come from, so I don't
know if this would be an issue.
Ben
--
Sent via pgsql-sql mailing list (pgsql-sql@p
At 12PM -0800 on 20/02/13 you (Sergey Konoplev) wrote:
> On Wed, Feb 20, 2013 at 10:16 AM, Ben Morrow wrote:
> >> If you got mixed up with plpgsql anyway what is the reason of making
> >> this WITH query constructions instead of implementing everything in a
> >> pl
At 8AM -0800 on 20/02/13 you (Sergey Konoplev) wrote:
> On Wed, Feb 20, 2013 at 12:19 AM, Ben Morrow wrote:
> > That's not reliable. A concurrent txn could insert a conflicting row
> > between the update and the insert, which would cause the insert to fail
> > with a u
Quoth gray...@gmail.com (Sergey Konoplev):
> On Sat, Feb 16, 2013 at 11:58 PM, Ben Morrow wrote:
> > WITH "exp" AS ( -- as before
> > ),
> > "subst" AS (
> > SELECT add_item(e.basket, e.nref, e.count)
> >
date,
enddate date,
exclude using gist
(object_id with =, box(startdate, enddate) with &&)
);
You have to use 'box' rather than 'lseg' because there are no indexes
for lsegs. I don't know how efficient this will be, and of course
tential difficulty calculating that time, you would
need to be able to wake up that client early if one of the tables
changed. Setting triggers on the tables to send a notify to that client
(probably a different notify from the one that client then sends out to
other clients) should be sufficient, a
select "server_id", "item_id"
from "upsert")
The third is that upsert is not as simple as you think. It isn't
possible (at least, not in Postgres) to take a lock on a row which
doesn't exist, so it's possible that a concurrent transaction
INCT e.msg
FROM "exp" e
LEFT JOIN "subst" s ON FALSE
WITH "exp" AS ( -- as before
)
SELECT DISTINCT s.msg
FROM (
SELECT e.msg, CASE
WHEN e.nref IS NULL THEN NULL
ELSE add_item(e.basket, e.nref, e.count)
Hi, I have some sql like so:
SELECT min(date) INTO d FROM interest_rate WHERE m_code = NEW.code;
UPDATE interest_rate SET date = NEW.start_date, rate = NEW.initial_rate
WHERE m_code = NEW.code AND date = d;
Actually this is pgsql but I don't think that matters.
I am wondering if I can make this
f the backup isn't done right the backed-up copy
may be corrupt.) Have you done a restore recently?
- Something I haven't thought of :).
- A bug in Pg. While this is *extremely* unlikely, it must be
mentioned as a possibility.
Ben
--
Sent via pgsql-
create table person_food (
person_id int not null references person,
food_id int not null,
food_group_id int not null,
foreign key (food_id, food_group_id)
references food (id, food_group_id),
unique (person_id, food_group_id)
);
If you wis
pgSQL functions were compiled at
CREATE FUNCTION time, using the SEARCH_PATH currently in effect. Is that
wrong? Is there some GRANT I'm missing that will make this work?
Ben
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.post
Quoth t...@sss.pgh.pa.us (Tom Lane):
> Ben Morrow writes:
> > I am trying to implement a fairly standard 'audit table' setup, but
> > using rules instead of triggers (since it should be more efficient).
>
> Rules are sufficiently tricky that I would never, ever re
is incremented twice), but is there any way to prevent it? Some
way of 'materialising' the NEW row so it is just plain values rather
than a list of expressions?
Ben
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
nct (id, name, age) as tuple from profile)
as profile;
Ben
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[quoting fixed]
Quoth dennis :
> Ben Morrow wrote:
> > Quoth dennis:
> >> Dear Ben
> >>
> >> thanks for you anwser.
> >> I try to add function quote_literal on my sql statement .
> >>
> >> but it raise other error message
Quoth dennis :
> Dear Ben
>
> thanks for you anwser.
> I try to add function quote_literal on my sql statement .
>
> but it raise other error message (quote_literal not support bytea format):
> function quote_literal(bytea) does not exist
Which Postgres versio
Quoth dennis :
> Hi Ben
>
> here is my function , it's for fix missing chunk problem.
> It has same problem ,please take look
>
>
> thank for you help
>
> -table--
>
>
> db=# \d usersessiontable;
>
xist: text || bytea
>
>
> But if my sql statement has no column "content"
> the sql works.
> sql:
> create table mail_new as select sender,subject from mail sender='dennis'
No, it still doesn't give that error for me. Show us something you've
*actual
* from a where c1=xxx
'AS' isn't valid there. What is xxx? Is it a field you haven't shown us,
or is it a quoted string?
> error:
> operator does not exist: text || bytea
That command (with 'xxx' quoted and the AS removed) doesn't give that
error with those
b' where id=nextval('cy30')::int;
UPDATE 30
B) update tc set des='c' where id=currval('cy30');
UPDATE 1
Regards,
Ben K.
Developer
http://benix.tamu.edu
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Another version along that line ?
# create sequence counterseq start 1;
-- (set/reset whenever a counter is needed)
# select main_table.*, nextval('counterseq') as position2
into sorted_main_table
from main_table, keytable where main_table.id =
keytable.main_table_id
order by value;
pdate main_table set position = ( (select oid::int4 from temp_table
where id = main_table.id ) - (select min(oid::int4) from temp_table) + 1)
;
I read that oid wraps around (after ~ billions) so you might want to check
your current oid.
Regards,
Ben K.
Developer
h
n was Kashmira Patel.
Sorry, I apologize.
Regards,
Ben K.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
nt4(id) ...;
worked.
I tried int8(id) but java didn't take it as Integer. (It took int8 as
something like Long.)
Regards,
Ben
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail
Current_user
Timestamp
"The Select Statement Submitted by the User"
http://www.postgresql.org/docs/8.1/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE
might be close to what you want.
Regards,
Ben K.
Developer
http://benix.tamu.edu
---(end of
file '/tmp/penchal.out' for writing: no such file or directory..
_IF_ you're on linux it could be due to selinux. setenforce 0 might solve
the problem tempoarily. I would assume there should've been some existing
discussion threads. (setenforce 1 afterwards
tupple number seems overly assumptive.
If it works for you great, your example may then be useful as a short
cut, but I don't believe in leaving things to chance when programming.
Regards,
Ben K.
Developer
http://benix.tamu.edu
---(end of broadcast)---
ng(array(select name from city where
idstate = s.idstate order by name),',') from state s;
==
name | array_to_string
--+-
RP | Gramado,Port Alegre
SP | Osasco
* I see normal
t( $1 ) || '' (SELECT * FROM
links WHERE p= $2 )''
CONTEXT: PL/pgSQL function "traverse" line 10 at execute statement
LINE 1: SELECT ''INSERT INTO '' || quote_ident( $1 ) || '' (SELECT ...
Regards,
Ben K.
Developer
http://benix.tamu.edu
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
to deliver the
templist table name as argument. (Somehow there seemed to be a bug(?)
there)
Regards,
Ben K.
Developer
http://benix.tamu.edu
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
ns over keeping one more field.
But I think this is more of an exercise, and functions would be the proper
way for complex operations.
Regards,
Ben K.
Developer
http://benix.tamu.edu
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
,50);
-- TRAVERSE
begin;
declare mc cursor for select * from linkedlist order by nextnode;
fetch 1 from mc;
fetch 1 from mc;
...
close mc;
commit;
which is nothing more than,
select * from linkedlist order by nextnode;
Regards,
Ben K.
Developer
http://benix.tamu.edu
above query included indexes too (relkind=i) while
information_schema.tables included only tables and views (r,v).
Ben K.
Developer
http://benix.tamu.edu
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
he
variable NEW - the NEW variable will store the row being deleted.
Best regards,
Ben Stewart
--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Student Software Engineer (RBAU/EQS4)
Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA
Tel: +61 3 9541-
east 50% faster than my stored
> procedure, so that would be ok.
>
> However, this has made me thinking. Couldn't this be done directly in
> SQL?
You could probably implement this as a trigger on the table for INSERT.
Have a squiz through the documentation on triggers.
Best regards,
Be
_UNKNOWN
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
--
In this code, whenever an exception is raised, the system will rollback
to the start of the block (BEGIN).
Best regards,
Ben Stewart
--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services,
-- <== Problem is here
> END LOOP;
> RETURN;
> END;
> $$ LANGUAGE 'plpgsql';
You need to declare this as a set-returning function if you're using
RETURN NEXT.
Best regards,
Ben Stewart
--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Servi
ation for $PL is read before creating triggers"?
It may also be worth including a comment that explicitly states that
all(?) PLs are supported in triggers.
Best regards,
Ben Stewart
--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Student Software Engineer (RBAU/EQS4)
Locked
alculate this,
then sort by that column.
Best regards,
Ben Stewart
--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Student Software Engineer (RBAU/EQS4)
Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA
Tel: +61 3 9541-7002 Fax: +61 3 9541-7700
mailto:[EMAIL PROTECTED]
http
INTO funcname;
WHILE FOUND LOOP
FETCH curs INTO funcname;
EXECUTE 'GRANT EXECUTE ON FUNCTION ' || funcname || ' TO myuser';
END LOOP;
CLOSE curs;
END;
---END CODE---
Best regards,
Ben Stewart
--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services,
o is:
SELECT col2 INTO myValue FROM .......
Best regards,
Ben Stewart
--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Student Software Engineer (RBAU/EQS4)
Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA
mailto:[EMAIL PROTECTED]
http://www.bosch.com.au/
---
ype;
UPDATE mytable SET col_to_move_2 = col_to_move;
ALTER TABLE mytable DROP COLUMN col_to_move;
ALTER TABLE mytable RENAME col_to_move_2 TO col_to_move;
COMMIT WORK;
Best regards,
Ben Stewart
--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Student Software Engineer (RBAU/EQS4)
WHERE table1.user = table3.employeeno
AND table1.course = table2.courseid;
What errors are you getting?
Best regards,
Ben Stewart
--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Student Software Engineer (RBAU/EQS4)
Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA
Te
-- E_UNKNOWN
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
------
Best regards,
Ben Stewart
--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Student Software Engineer (RBAU/EQS4
s and returning distinct
values with the BEGIN.. RAISE EXCEPTION.. EXCEPTION.. END method
proposed in previous mailing list posts.
Are there any suggestions on how to implement this?
Best regards,
Ben Stewart
--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Software Engineer (RBAU
To:
pgsql-sql@postgresql.org
We will now
place any business with a qualified website at the top of the major search
engines: Yahoo!, MSN, Alta Vista, etc. This promotion is not going to last
long. If you are interested in being guaranteed first position in the top
search engines at a pr
Title: Message
There is a very
common technique used in other RDBMS (e.g.
Sybase) stored procedure
programming which I am not sure how best to replicate in Postgres
functions.
A Sybase stored
procedure can do
select into #temp1
create table
#temp2 (…)
call some proc
which also u
-05 4 5 0 0 9
Any ideas?
Thanks,
Ben
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.521 / Virus Database: 319 - Release Date: 9/23/2003
---(end of broadcast)---
Is there a query that will return all the databases available, similar
to what psql -l does?
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
a file which was created using the pg_dump
command.
I have recently upgraded from 7.2.1 to 7.2.3 using the RPM.
Thanks in advance,
ben
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-noma
unlimited text fields ;-)
Regards
Ben
> -Original Message-
> From: Koen Antonissen [mailto:[EMAIL PROTECTED]]
> Sent: 30 March 2001 12:52
> To: [EMAIL PROTECTED]
> Subject: [SQL] Max Size of a text field
>
>
> Hi there
>
> I just recieved this error:
>
Title: RE: [SQL] Help with 'now', now(), timestamp 'now', ...
try timeofday(), now() gives starttime of transaction :-{.
Regards
Ben
PS this has just been discussed in this mailing list or I would have been clueless
> -Original Message-
> From: [EMAIL
Title: RE: [SQL] readline ??
Try the new RPMs: postgresql-7.0.3-2mdk. They include readline support.
Ben
> -Original Message-
> From: vs [mailto:[EMAIL PROTECTED]]
> Sent: 15 December 2000 10:12
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
Title: RE: [SQL] to_char() causes backend to close connection
select to_char(now()-'5 minutes'::interval,'YYMMDDHH24MI') seems to work
Ben
> -Original Message-
> From: Kovacs Zoltan Sandor [mailto:[EMAIL PROTECTED]]
> Sent: 14 December 2000 15:49
6 | Group2-1 | 5
Just for testing. I can see how it should work but the client side code is defeating me at the moment. I'm working in delphi with winzeos (cracking components by the way). Anyone done this.
Thanks for any help
Ben Trewern
> -Original Message
Title: Tree structure
Anybody know how to make a tree structure using related tables using Postgres. Something like a directory structure is what I'm aiming for. I'm sure there is an easy way but I'm having probs.
Any help would be appreciated.
Ben.
Does postgresql allow (or plan to provide) a select
outside the current database?
Eg. (In Sybase parlance)
select * from otherdb..table
Thanks, Ben
--
_
Ben Stringer[EMAIL PROTECTED]
-
66 matches
Mail list logo
