Re: [SQL] Encoding bytea
O kyrios Joe Conway egrapse stis Mar 3, 2004 :
> 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 with generating insert statements in xml files
> > for remote processing.
>
> Is this what you need?
Yes thanx. ( i always forget about the casting function trick)
>
> create table t(f bytea);
> insert into b values ('a\\003\\000\\001b');
>
> create or replace function bytea2text(bytea) returns text as '
> begin
> return $1;
> end;
> ' language plpgsql;
>
> regression=# select 'insert into t values(' ||
> quote_literal(bytea2text(f)) || ');' from t;
>?column?
>
> insert into t values('a\\003\\000\\001b');
> (1 row)
>
> regression=# insert into t values('a\\003\\000\\001b');
> INSERT 292656 1
>
> HTH,
>
> Joe
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
--
-Achilleus
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] sub-select parameter problem
> > Hello, > > Imagine the following query: > > --- > SELECT > > tableA.field1, > tableA.field2, > =20=20 > tableB.field1, > tableB.field2, > > ( > SELECT tableC.field2 > FROM tableC > WHERE tableC.field1 =3D tableB.field1 - 1; > ) AS p > > FROM tableA > INNER JOIN tableB > ON tableA.pk =3D tableB.FK; > --- > > It works fine. > > Now, I need to do something else: the parameter of my sub-select is also > a member of the table I'm selecting. > > --- > SELECT > > tableA.field1, > tableA.field2, > =20=20 > tableB.field1, > tableB.field2, > > ( > SELECT tableB.field2 > FROM tableB > WHERE tableB.field1 =3D tableB.field1 (--> from-main-select?) - 1; > ) AS p > > FROM tableA > INNER JOIN tableB > ON tableA.pk =3D tableB.FK; > --- > > How can I refer to the tableB.field1 parameter from the main query? I've > tried to do something like this, but without success: > [snip] If the tableC example works fine - this should do too SELECT tableA.field1, tableA.field2, tableB.field1, tableB.field2, ( SELECT tB.field2 FROM tableB tB WHERE tB.field1 = tableB.field1 - 1; ) AS p FROM tableA INNER JOIN tableB ON tableA.pk = tableB.FK; HTH Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] sub-select parameter problem
On Thursday 04 March 2004 11:20, Philippe Lang wrote: [working query] > It works fine. Excellent! :-) Oh - there's more :-( > Now, I need to do something else: the parameter of my sub-select is also > a member of the table I'm selecting. > > SELECT > > tableA.field1, > tableA.field2, > > tableB.field1, > tableB.field2, > > ( > SELECT tableB.field2 > FROM tableB > WHERE tableB.field1 = tableB.field1 (--> from-main-select?) - 1; > ) AS p > > FROM tableA > INNER JOIN tableB > ON tableA.pk = tableB.FK; > --- > > How can I refer to the tableB.field1 parameter from the main query? I've > tried to do something like this, but without success: You're along the right lines, except I think you want to alias the table not the column. ... ( SELECT x.field2 FROM tableB AS x WHERE x.field1 = tableB.field1 - 1 ) ... HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] How to avoid (stop) a endless loop in a trigger
Hi people, i have a problem here. I'm doing a trigger that when a update occurs i need to do an update on the same table (target table), but as known, it causes a endless loop whithin infinit updates. So I need to stop the trigger after it does the first update, is there any way? I tried to do a return null, but that was a very bad idea because it stops completly the function fired by a trigger and all its computation is in vain... The test trigger that i did is like : CREATE OR REPLACE FUNCTION public.sp_teste_loop() RETURNS trigger AS ' begin raise notice \'Trigger Fired\'; if (TG_OP = \'INSERT\') then update teste_trigger set flg_bool = \'S\' where codigo=NEW.codigo; RETURN NEW; elsif (TG_OP = \'UPDATE\') then update teste_trigger set flg_bool = \'N\' where codigo=NEW.codigo; RETURN NULL; end if; end; ' LANGUAGE 'plpgsql' VOLATILE; CREATE TRIGGER tr_sp_teste_trigger BEFORE INSERT OR UPDATE ON public.teste_trigger FOR EACH ROW EXECUTE PROCEDURE public.sp_teste_loop(); Thank for any help and regards = Rodrigo Sakai Database Programmer [EMAIL PROTECTED] http://www.2bfree.com.br Tel: (55) (11) 5083-5577 Fax: (55) (11) 5549-3598 = ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to avoid (stop) a endless loop in a trigger
On Thu, 2004-03-04 at 14:54, Rodrigo Sakai wrote: > Hi people, i have a problem here. > I'm doing a trigger that when a update occurs i need to do an update on the same > table (target table), but as known, it causes a endless loop whithin infinit > updates. So I need to stop the trigger after it does the first update, is there > any way? > I tried to do a return null, but that was a very bad idea because it stops > completly the function fired by a trigger and all its computation is in vain... > > The test trigger that i did is like : > > CREATE OR REPLACE FUNCTION public.sp_teste_loop() RETURNS trigger AS ' > begin > raise notice \'Trigger Fired\'; > if (TG_OP = \'INSERT\') then > update teste_trigger > set flg_bool = \'S\' > where codigo=NEW.codigo; > > RETURN NEW; > > elsif (TG_OP = \'UPDATE\') then > update teste_trigger > set flg_bool = \'N\' > where codigo=NEW.codigo; > > RETURN NULL; > end if; > end; > ' LANGUAGE 'plpgsql' VOLATILE; Does this update other records, or only the one you are inserting or updating? If the former, add " AND flg_bool IS NULL OR flg_bool != \'S\'" to the update condition (!=\'N\' for the update case); then records that are already OK will not be touched, so the recursion will stop automatically. If the latter, just change NEW.flg_bool and return NEW > CREATE TRIGGER tr_sp_teste_trigger > BEFORE INSERT OR UPDATE > ON public.teste_trigger > FOR EACH ROW > EXECUTE PROCEDURE public.sp_teste_loop(); > > Thank for any help and regards > > > = > Rodrigo Sakai > Database Programmer > [EMAIL PROTECTED] > http://www.2bfree.com.br > Tel: (55) (11) 5083-5577 > Fax: (55) (11) 5549-3598 > = > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] query optimization
All,
I have the following query which is running quite slow on our server and
was hoping someone would have suggestions how I might improve it.
est3=>EXPLAIN SELECT clone.uniquename,clone.name,library.type,clone.clone_id
est3-> FROM library,clone_aceg
est3-> JOIN clone USING (clone_id)
est3-> WHERE clone_aceg.aceg_id = 8 AND
est3-> clone.project=library.project;
QUERY PLAN
-
Nested Loop (cost=0.00..27.92 rows=1 width=57)
Join Filter: (("outer".project)::text = ("inner".project)::text)
-> Nested Loop (cost=0.00..18.55 rows=4 width=43)
-> Index Scan using aceg_id_clone_aceg_key on clone_aceg (cost=0.00..3.05
rows=4 width=4)
Index Cond: (aceg_id = 8)
-> Index Scan using clone_pkey on clone (cost=0.00..3.91 rows=1 width=39)
Index Cond: ("outer".clone_id = clone.clone_id)
-> Seq Scan on library (cost=0.00..2.15 rows=15 width=14)
(8 rows)
relevant tables below.
regards,
Charles
Tables:
Table "public.clone"
Column | Type | Modifiers
+---+
clone_id | integer | not null default
nextval('"clone_clone_id_seq"'::text)
name | character varying(10) | not null
uniquename | text | not null
project| character varying(8) |
p_end | character varying(2) |
lib_id | integer |
accn | character varying(10) |
seq| text | not null
seqlen | integer |
hq_start | integer |
hq_end | integer |
scaffold | character varying(50) |
Indexes: clone_pkey primary key btree (clone_id),
clone_uniquename_idx unique btree (uniquename),
clone_accn_idx btree (accn),
clone_name_idx btree (name),
clone_project_idx btree (project),
clone_scaf_idx btree (scaffold)
Table "public.library"
Column| Type | Modifiers
-+-+
lib_id | integer | not null default nextval('"library_lib_id_seq"'::text)
source | text|
type| text|
project | integer |
name| text|
organism| text|
strain | text|
vector | text|
rs1 | text|
rs2 | text|
preparation | text|
Indexes: library_pkey primary key btree (lib_id),
library_project_idx btree (project),
library_type_idx btree ("type")
Table "public.clone_aceg"
Column | Type | Modifiers
--+-+---
clone_id | integer |
aceg_id | integer |
Indexes: clone_aceg_clone_id_key unique btree (clone_id, aceg_id),
aceg_id_clone_aceg_key btree (aceg_id),
clone_id_clone_aceg_key btree (clone_id)
Foreign Key constraints: cloneid FOREIGN KEY (clone_id) REFERENCES clone(clone_id) ON
UPDATE NO ACTION ON DELETE CASCADE,
acegid FOREIGN KEY (aceg_id) REFERENCES aceg(aceg_id) ON
UPDATE NO ACTION ON DELETE CASCADE
List of relations
Schema |Name| Type | Owner |Table
++---+-+--
public | aceg_aceg_idx | index | chauser | aceg
public | aceg_assembly_key | index | chauser | aceg
public | aceg_blast_aceg_id_key | index | chauser | aceg_blast
public | aceg_contig_idx| index | chauser | aceg
public | aceg_g_scaffold_idx| index | chauser | aceg
public | aceg_has_blast_idx | index | chauser | aceg
public | aceg_id_aceg_blast_key | index | chauser | aceg_blast
public | aceg_id_clone_aceg_key | index | chauser | clone_aceg
public | aceg_pkey | index | chauser | aceg
public | aceg_uniquename_idx| index | chauser | aceg
public | blast_id_aceg_blast_key| index | chauser | aceg_blast
public | blast_id_contig_blast_key | index | chauser | contig_blast
public | blast_ortho_idx| index | chauser | blast
public | blast_pkey | index | chauser | blast
public | clone_accn_idx | index | chauser | clone
public | clone_aceg_clone_id_key| index | chauser | clone_aceg
public | clone_contig_clone_id_key | index | chauser | clone_contig
public | clone_id_clone_aceg_key| index | chauser | clone_aceg
public | clone_id_clone_contig_key | index | chauser | clone_contig
public | clone_name_idx | index | chauser | clone
public | clone_pkey | index | chauser | clone
public | clone_project_idx | index | chauser | clone
public | clone_sca
[SQL] debugging query to put message in pg logfile?
[postgresql-7.4RC2, python-2.3.3, PyGreSQL-3.4, SuSE x86 Linux 8.2] I've started putting debugging queries like: select "opwin.py: committing step signoff" in my app, just to have an entry in the postgres logfile. These are especially helpful in tracking down what piece of code did a 'commit', since there's nothing to distinguish one from another in the log. Is there some cheaper (or more appropriate) sql statement that will show up in the postgres log? I thought I remembered a "message" sql statement or something like that. -- George Young -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] returning a recordset from PLpg/SQL
Terence Kearns wrote:
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 case to lower case for unquoted names.
>
>
>
> Also, you can hardly expect a function to return a rowtype that doesn't
> even exist until the function executes --- how the heck is the parser
> supposed to make sense of the calling query? So the "execute create
> type" part of this is nonsense, I'm afraid.
Right you are. I did mention that I didn't expect that code to work at
all, I just used it as an indicator or a desired outcome. I'm sorry if
I didn't make that clear enough. I just hoped that it would illustrate
what I'm trying to achieve. And that is:
"return a set of rows where the columns in that row are not yet
determined."
> The SETOF RECORD mechanism
> will let you return a rowtype that is not known fully at the time the
> function is written, but the rowtype does have to be known when the
> calling query is parsed.
Interesting.
>
> You might be able to replace the CREATE TYPE with an anonymous record
> type in the calling query:
>
> select ...
> from details_for_profile(...) as x(doc_id int4,
>doc_title varchar(256),
>...);
>
> regards, tom lane
>
yeah but then you're back to square one with dynamically building the
columns to match the anonymous type you declared in the query.
I've got some ideas about creating persistent types using RULEs on the
attribute_profiles table. So when someone INSERTs or UPDATEs an
attribute profile, a datatype going by the name 'profile_type_' ||
att_profile_id::text is created. That way the types are already known
and maybe can somehow be passed to the details_for_profile() prcedure.
I'll have to experiment and get back.
I'm sorry if this is just plain stupid - as I've not bothered to read
the full schema in your message - but wouldn't a cursor do ?
Since I'm mostly coding in Java, I find that this approach, while common
to both PGSQL and Oracle, works quite good. I've not
been able to use the cursor in psql although... By the way can someone
tell me how the heck am I supposed to get the results
from a cursor return from a pl/pgsql function ?
Example:
create or replace function SomeObject_read(int) returns refcursor as
'
declare
the_row refcursor;
v_id alias for $1;
begin
open the_row for
select *
from SomeObject
where id = v_id;
return the_row;
end;
' language plpgsql;
This works great from Java where I use a callable statement, do a "{ ? =
call SomeObject_read(?) }" query,
register the out parameter as OTHER, bind the parameter and get back a
ResultSet.
All fine and marry, but I can't figure out how to use _this_ form of the
function from psql. I've read and tried
the manual examples and they do work. However, I can't make this one work.
Thanks in advance,
--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.
+40213212243
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] query optimization
Charles Hauser <[EMAIL PROTECTED]> writes: > I have the following query which is running quite slow on our server and > was hoping someone would have suggestions how I might improve it. Have you vacuumed or analyzed these tables recently? The EXPLAIN numbers show that the planner thinks all the tables are tiny, which hardly seems likely given that you're complaining about the speed. If it's still bad after you VACUUM ANALYZE, send EXPLAIN ANALYZE (not just EXPLAIN) output and maybe we can give some help. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] debugging query to put message in pg logfile?
george young <[EMAIL PROTECTED]> writes: > I've started putting debugging queries like: >select "opwin.py: committing step signoff" > in my app, just to have an entry in the postgres logfile. > Is there some cheaper (or more appropriate) sql statement that will show > up in the postgres log? You could just send SQL comments: -- opwin.py: committing step signoff One advantage of this is that you can merge the comments with actual commands, thus not incurring even a network round-trip time for them. If you do send it separately, it will act like an empty query string. People tend not to think of this because psql strips -- comments before sending commands. But I believe all the lower-level libraries will pass them through. (If you need to pass loggable comments through psql, I think the /* ... */ form will work.) regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
