Re: [SQL] Problems with pg_dump (on Debian i386)
Tom Lane <[EMAIL PROTECTED]> writes: > Could we see a specific example please --- preferably, enough of the > dump file to reproduce the problem? Also, exactly *what* version hop > are we talking about? Example file (extract of my dump file) is attached -- the misbehaviour occurs even when simply \i-ing this file in psql. Regarding version hops: I've followed Oliver Elphicks Debian (unstable) releases except the RC test releases and now I'm at 7.1.1. Not sure when this occured, but with 6.xx it worked for sure. Greetings and thanks for your help, joachim Example file for psql misbehaviour ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] START for SERIAL type?
When you create a SEQUENCE, you are allowed to specify a START. Is there a way to specify a START if you use a serial type in a CREATE TABLE statement? I'm envisioning something like: CREATE TABLE foo ( foo_idserial ( 101 ) ); ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Re: Stored Procedures?
On Thu, 24 May 2001 13:45:46 + (UTC), [EMAIL PROTECTED] (Tod McQuillin) wrote: >Postgres doesn't have stored procedures in the same way that other >databases like oracle and sybase do. But it does have stored functions, >and they can be used in almost exactly the same way. In Sybase I am used to the fact that stored procedures can return a result set to the client. Something like; create procedure sp_example (@param int) as begin select bla from tablename where somecolumn = @param end (of course in pratice stored procedures get a lot more complex than this :-) Is something like this posstible in PostgreSQL? -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen [EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: Return cursor
Can you send PL/SQL code and back-end code used this PL/SQL code? Myself trubles with CURSOR I resolve use LIMIT ... OFFSET ... Alla wrote: > I am porting our database from Oracle to PostgreSQL > > I know quite a lot about Oracle and pretty much nothing about > PostgreSQL :-)) > > I have a lot of stored procedures in Oracle that return result sets or > cursor. All I have to do there is open a cursor and calling > application can just fetch it > > Is there anyway to do the same thing in PostgreSQL? > > Please, help. So far I could not find anything ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: Return cursor
If you are planing to use ecpg :
exec sql declare cuserbase cursor for
select column1, column2, column3 from my_table order by 1;
exec sql open cuserbase;
exec sql whenever not found do break;
while ( true ) {
exec sql fetch cuserbase into ...
// do same work
}
exec sql close cuserbase;
Please read documentation for more detail.
Good luck.
Sergey.
"Alla" <[EMAIL PROTECTED]> ???/ ? ?:
news:[EMAIL PROTECTED]...
> Alexander Dederer <[EMAIL PROTECTED]> wrote in message
news:<9ehged$k4f$[EMAIL PROTECTED]>...
> > Can you send PL/SQL code and back-end code used this PL/SQL code?
> > Myself trubles with CURSOR I resolve use LIMIT ... OFFSET ...
>
> It would look something like this:
>
> create or replace package my_package
> AS
>type gc_cursor is ref cursor;
>
>procedure load_users
>(pp_user_base out gc_cursor);
> end my_package;
> /
>
> create or replace package body my_package
> as
> procedure load_users
> (pp_user_base out gc_cursor)
> as
> begin
>open pp_user_base for
> select column1, column2, column3
> from my_table
> order by 1;
>
> exception
>when others then
> raise_application_error(-20100, 'Error while trying to load user
base ' ||
> sqlerrm);
> end load_users;
>
> end my_package;
> /
>
> C program would look something like this:
>
> void
> load_user_base()
> {
> EXEC SQL BEGIN DECLARE SECTION;
>sql_cursor cUserBase;
> EXEC SQL END DECLARE SECTION;
>
> struct USER_PROFILE {
> ...
> } user_profile[ARRAY_LENGTH];
>
> struct USER_PROFILE_IND {
> ...
> } user_profile_ind[ARRAY_LENGTH];
>
> EXEC SQL ALLOCATE :cUserBase; /* allocate the cursor variable */
>
> EXEC SQL EXECUTE
> BEGIN
> my_package.load_users(:cUserBase);
> END;
> END-EXEC;
>
> EXEC SQL WHENEVER NOT FOUND DO break;
>
> for (;;) {
> EXEC SQL FOR :i FETCH :cUserBase INTO :user_profile:user_profile_ind;
>
> if (sqlca.sqlcode != 0) {
> fprintf(stderr, "Fetching users %s\n", sqlca.sqlerrm.sqlerrmc);
> break;
> }
> }
>
>
>
>
> Thanks for your response
>
> Alla
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Select for LEFT JOIN
Hi, I am trying to do a 2 joins between 3 tables. ie) select , coalesce(TRR.ABC, SOC.ABC) as newABC, ... from A join (B join C on (..)) on (..)) as TRR left join (D join E on (..)) as SOC on (TRR.Field1=SOC.Field2) When I run this it says that there is an ambiguous field. Yes after the join for TRR and SOC they both contain a fields name ABC. How can I rename this field in the sql statement or how can I make it so the sql statement know that they are different. Please help.. .thanks -- Linh Luong Computalog Ltd. Software Developer Phone: (780) 464-6686 (ext 325) Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] SQL specified sort
I was wondering if you could help me out with a problem I'm having. Say for example I have data coming out like: H29 214 200 099 How would I use a sort to be able to make the output look like 214 H29 200 099 I don't believe their is a way to do an ORDER BY in a specific order. Any help you could give me this problem would be very much appreciated. Jeff Morrison _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Triggers in 7.0.x and SQL syntax.
Well, after dinking around for serveral hours I decided to beg for help on here about triggers and look like an idiot all in one slew. I have two tables. A and B. Whenever A has a row inserted I want to grab that newly created primary key from it's row in table A and insert it into a field in table B. I have tried creating single triggers and triggers that call functions. I even messed with rules a bit. All resulted in either "something" happening but not seeing a change in data or flat out syntax or code errors. I was attempting to use: select last_value from A_A_id_seq;to get the latest primary key. This seems to work ok on it's own. Question is how do I incorporate that into a trigger or something equivalent? Also, what if I have tables C and D that need to be update the same as B is. Do I make more triggers or simply have the function called by the trigger or the trigger itself update all three tables? Thanks. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] sql user management
With SQL requests I can create, change privileges of the user... Can I access to user information only with sql-requests ? - Is the user a member of group ? - Has he rights to table ? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Ynt: where's ALTER TABLE table DROP [ COLUMN ] column???
Hi, I want to make a little correction. The correct syntax should be: SELECT [all columns except named] into mytemp1 from [$table_name] ; drop table [$table_name]; SELECT * into [$table_name] from mytemp1; With regards, Devrim GÜNDÜZ Keith Gray <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Bruno Boettcher wrote: > > > > Hello! > > > > as far as i can tell, i can add columns to a table, but can't remove > > them later on. > > > > is this true? > > My thought would be that somebody must have written an > automated version of > > select [all columns except named] from [named table] into droptemp; > drop [named table]; > select * from droptemp into [named table]; > > Would this be available or easily written as a "rule" ?? > Could anyone familiar with the code comment? > > -- > Keith Gray > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Difficult SQL Statement
I have a table that has the following columns: AUTHOR_NO, ASMT_CODE, & STATUS. The table would look something like this: AUTHOR_NO ASMT_CODE STATUS 12345 1 PASSED 12345 2 FAILED 12345 3 FAILED 12345 4 PASSED 12346 1 PASSED 12346 2 PASSED 12346 3 PASSED 654321 1 FAILED 654321 2 PASSED 654321 3 FAILED 654321 4 FAILED 000123 1 PASSED So I am trying to write a SQL statement that will return the ASMT_CODE, the total number of ‘PASSED’ for the ASMT_CODE, the total number of participants for that ASMT_CODE and finally a percent of the PASSED for that particular ASMT_CODE over the number of participants for that ASMT_CODE. So, if I had the table above I would get something like this: ASMT_CODE # PASSEDTOTAL # % of Total 1 3 4 75 2 2 3 66.67 3 1 3 33.34 4 1 2 50 As you notice I am look for the ASMT_CODE base percentage rather than the over all percentage. What would be the SQL to do this? I have tried to write this, but cannot figure out how to calculate the last two columns. Here is what I have so far: select d1.asmt_code, count(d1.amst_code) from test_run d1 where d1.status = 'PASSED' group by d1.asmt_code order by d1.asmt_code BUT this only yields me the first two columns. CAN ANYONE HELP? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Ynt: corrupted tables !!!
Hi, What kind of a corruption? Do you lose data? As far as I tested; bas transactions do not destroy db. With regards, Devrim "J.Fernando Moyano" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > > I have big problems with my postgres. > There is a table that gets corrupted frecuently !!! (every week ...) > I have to drop the table and create it again using the backup copy. (normally > i lose some data) > > Can it gets corrupted bucause of bad usage of transactions (ie. transactions > not committed or rollbacked) ?? > > And an intensive usage of restrictions, foreign keys, etc... ??? > > Thanks !! > > > - -- > Fernando Moyano > > Frase del día: > - -- > Linux es aburrido... con Windows nunca sabes cuándo se va a colgar. > > (*) SymeX ==> http://symex.lantik.com > (*) WDBIL ==> http://wdbil.sourceforge.net > (*) Informate sobre LINUX en http://www.linux.org > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.0.4 (GNU/Linux) > Comment: For info see http://www.gnupg.org > > iD8DBQE7D8EQoZaf9MvtDvcRArX0AKCmj7oqUaWsKi4245SKqeUEU2/QvwCcDY1L > sn48O86W3DRkSbnXJeqB/5U= > =XHJm > -END PGP SIGNATURE- > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Re: batch file
Create the file for example command.sql and log into the database psql mydbase and write command \i command.sql U¿ytkownik "Milen" <[EMAIL PROTECTED]> napisa³ w wiadomo¶ci [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Hi all!, > > I want to execute several SQL statements one after another. > Does anyone know if it is possible to create something like a "batch file" > which contains all SQL commands I want. > > Best Regards: > Milen > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Recursive select
Hi all! Anyone know if it's possible to make a recursive select from a table ? My problem: I got a table of "some categories" which all points to its parrent one(tree)...shown below. And I want to select all names of parrent categories of one child, lets say "fast[4]". Now I'm solving that with many SQL queries like : "SELECT main_id FROM cat WHERE id=4;"but I would like to optimize this. Anyone can help or point me to a way ?? Thanks a lot,Minca Table : CAT ID|Main_id|Name = 10Car 21Crash 31Wash 43Fast 51Second_hand 60House 73Slow etc *(root)[0] -Car[1] -Crash[2] -Wash[3] -Fast[4] -Slow[7] -Second hand[5] -House[6] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] altering date type of a column
Hi, I have a table in one of my databases which has a row of type char. I would like to convert it to int data type. Is there an easy way for doing it? With regards, Devrim GÜNDÜZ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] sql user management
With SQL requests I can create, change privileges of the user... Can I access to user information only with sql-requests ? - Is the user a member of group ? - Has he rights to table ? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] SQL specified sort
Jeff, If you want to receive your data in an order which you specify manually, the easiest way to accomplish this is to add a separate field which contains an int4 value used by you to specify the order. E.g. myval | sortint _ H29| 2 214| 1 200| 3 099| 4 SELECT myval FROM mytable ORDER BY sortint; Troy > > I was wondering if you could help me out with a problem I'm having. Say for > example I have data coming out like: > H29 > 214 > 200 > 099 > > How would I use a sort to be able to make the output look like > > 214 > H29 > 200 > 099 > > I don't believe their is a way to do an ORDER BY in a specific order. Any > help you could give me this problem would be very much appreciated. > > Jeff Morrison > _ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Seq Scan
Hi, I am trying to execute this query I wrote and it is not returning at a reasonable time to the browser. I took the query and ran EXPLAIN on it and it displayed that every JOIN I do it is doing a Seq Scan on it. I have indexes on that table but it doesn't seem to use it. Is there a way I can ensure that postgres will use my indexes? Thanks again -- Linh Luong Computalog Ltd. Software Developer Phone: (780) 464-6686 (ext 325) Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Problems with pg_dump (on Debian i386)
Joachim Trinkwitz <[EMAIL PROTECTED]> writes: > CREATE TABLE "lplan" ( > ... > "art" character[] NOT NULL, I believe that PG 7.1 interprets that field declaration as "art" character(1)[] NOT NULL, and then truncates your input to match. 7.2-to-be rejects the input with complaints like psql:germdb.sql:20: ERROR: value too long for type character(1) which is the SQL-specified behavior for char(n) fields. Since you appear to be using different string lengths in your data, I doubt char(n) is the right datatype to begin with. I suggest "art" varchar(n)[] NOT NULL, for some appropriate n. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] ERROR: parser: parse error at or near "$1"
HI all, I am getting this weird syntax error i.e. parse error at or near "$1" and I am not even passing any arguement to my pl/pgsql function. Here is my function , please someone could elobarte before I go nuts :) drop function populate_art_details(); create function populate_art_details() returns bool as' declare labrecord; art record; coll_ids text; lab_ids text; del text; begin del:=''|''; lab_ids:=; coll_ids:=; FOR art IN select r.artist_id,r.crh_id,r.coll_id,r.label_id, a.name,a.extrinfo from artist a, releases r where r.artist_id=a.artist_id loop FOR lab IN SELECT coll_id,label_id from releases where artist_id = art.artist_id loop coll_ids:=coll_ids||del||lab.coll_id; lab_ids:=lab_ids||del||lab.label_id; end loop; coll_ids:=coll_ids||del; lab_ids:=lab_ids||del; insert into artist_details( artist_id,crh_id,labels_id, coll_ids,name,info) values (art.artist_id,art.crh_id,lab_ids,coll_ids,art.name,art.extrinfo); end loop; return ''t''; end; ' language 'plpgsql'; select populate_art_details(); Regards, -- Najm Hashmi Tel:514-271-9791 www.mondo-live.com www.flipr.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Select for LEFT JOIN
> "LL" == Linh Luong <[EMAIL PROTECTED]> writes: LL> Hi, LL> I am trying to do a 2 joins between 3 tables. LL> ie) LL> select , coalesce(TRR.ABC, SOC.ABC) as newABC, ... LL> from A join (B join C on (..)) on (..)) as TRR LL> left join LL> (D join E on (..)) as SOC on (TRR.Field1=SOC.Field2) LL> When I run this it says that there is an ambiguous field. Yes after the LL> join for TRR and SOC they both contain a fields name ABC. How can I LL> rename this field in the sql statement or how can I make it so the sql LL> statement know that they are different. LL> Please help.. .thanks You have to use full-qualifyed field names, like this: select trr.abc, soc.abc, In addition you can use aliases for field names: select trr.abc as abc1, soc.abc as abc2, -- Anatoly K. Lasareff Email:[EMAIL PROTECTED] http://tolikus.hq.aaanet.ru:8080 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
