[SQL] Why their is a limit in Postgresql (psql) Parameters..?
Hi All, I'm using Postgresql v7.3.3. I have a small question ... Why is that there is a maximum limit of 32 input parameters to thePostgresql function? Whereas stored procedures in Oracle and SQL Servertake more than 32 input arguments. So this puts extra burden on themiddleware developer to handle this stiuation at the time of migratingexisting databases in SQL Server or Oracle to Postgresql. Any Info/Suggestions will be highly appreciated. Are you Unmarried? Register in India's No 1 Matrimony
[SQL] Postgresql Temporary table scripts..
Hi, I'm using Postgresqlv7.3.3. Actually my requirement was to create one temporary table and insert some values on it and finally return the inserted values. For this simple thing i'm struggling a lot with two errors. one is 'RELATION '' ALREADY EXISTS' -- This is happening when ever i called the function more than ones in the same connection. To avoid this, i had created a nested function, In inner function i had created the temporary table and inserted some values and finally i called the return values on outter fucntion. this time i get the error as 'RELATION 'x' DOES NOT EXIST'. I hope u all understood my problem.. if any of u send some sample example to work around this problem will be highly appreciated. Thanks in advance, Vijay Watch Hallmark. Enjoy cool movies. Win hot prizes!
[SQL] Equivalent to sql%rowcount or @@rowcount
Hi All! is it possible to get in sql number of rows affected by the sql last insert, update or delete statement?? for eg, oracle - sql%rowcount. sqlserver select @@rowcount. Any help will be highly appreciated.. Thanks Regards, VijayCool new emoticons. Lots of colour! On MSN Messenger V6.0
[SQL] problem in database backup
Hi All, I'm using Postgresql V7.3.3, on that i created one database with name "mydb". PostgreSQL 7.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7) While taking a backup of my postgresql database, i'm getting the below error. bash-2.05b$ pg_dump mydb >mydb.outpg_dump: server version: SAGES/pg_dump.mo; pg_dump version: 7.2.2pg_dump: aborting because of version mismatch (Use the -i option to proceed any bash-2.05b$ pg_dump -i mydb > mydb.outpg_dump: server version: SAGES/pg_dump.mo; pg_dump version: 7.2.2pg_dump: proceeding despite version mismatchpg_dump: query to obtain list of data types failed: ERROR: Attribute "typprtlen" not found== After getting these errors, i tried to check the version of pg_dump on my system by using the below command. Here is the result . [EMAIL PROTECTED] root]# find / -name pg_dump -print/root/postgresql-7.3.3/src/bin/pg_dump/root/postgresql-7.3.3/src/bin/pg_dump/pg_dump/usr/bin/pg_dump/usr/local/pgsql/bin/pg_dump Kindly guide me to solve this problem. .. Thanks in advance, With Regards, Vijay It's all happening @ F1. Feel the thrill! Race along right here!
Re: [SQL] problem in database backup
Hi Tomasz Myrta, Thanks for ur info, u r correct .. after pointing to /usr/bin/pgsql/bin/pg_dump, i'm able to take backup.. Thank u very much.. With Regards Vijay >From: Tomasz Myrta <[EMAIL PROTECTED]> >To: vijaykumar M <[EMAIL PROTECTED]> >CC: [EMAIL PROTECTED] >Subject: Re: [SQL] problem in database backup >Date: Thu, 31 Jul 2003 13:40:45 +0200 > >Dnia 2003-07-31 13:13, U¿ytkownik vijaykumar M napisa³: > >> >>Hi All, >> >>I'm using Postgresql V7.3.3, on that i created one database with >>name "mydb". >> >>* PostgreSQL 7.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) >>3.2 20020903 (Red Hat Linux 8.0 3.2-7)* >> >>While taking a backup of my postgresql database, i'm getting the >>below error. >> >>bash-2.05b$ *pg_dump mydb >mydb.out >>pg_dump: server version: SAGES/pg_dump.mo; pg_dump version: 7.2.2 >>pg_dump: aborting because of version mismatch (Use the -i option >>to proceed any >>* >> >>*bash-2.05b$ pg_dump -i mydb > mydb.out >>pg_dump: server version: SAGES/pg_dump.mo; pg_dump version: 7.2.2 >>pg_dump: proceeding despite version mismatch >>pg_dump: query to obtain list of data types failed: ERROR: >>Attribute "typprtlen" not found >>==* >> >>After getting these errors, i tried to check the version of pg_dump >>on my system by using the below command. Here is the result . >> >>[EMAIL PROTECTED] root]# find / -name pg_dump -print >>/root/postgresql-7.3.3/src/bin/pg_dump >>/root/postgresql-7.3.3/src/bin/pg_dump/pg_dump >>/usr/bin/pg_dump >>/usr/local/pgsql/bin/pg_dump >>* > >Probably you have both 7.2.2 and 7.3.3 pg_dump versions and PATH >points to 7.2.2 /usr/bin/pg_dump. Try using >/usr/local/pgsql/bin/pg_dump > >Regards, >Tomasz Myrta > > It's all happening @ F1. Feel the thrill! Race along right here!
[SQL] createlang plpgsql failing on redhatlinux7.2
Hi All, I'm getting some problem on "createlang plpgsql". with REDHATLINUX7.2 These are all my system configurations.. We have a machine with RedhatLinix 7.2, on top of this i upgraded the postgresql7.3.3 version. After creating a database, i'm trying to create a language for that database by using createlang plpgsql it throws an error " failed with some missing files". so, i copied /usr/local/pgsql/plpgsql.so from Redhatlinux8.0 machine and tried again to create a language. this time i got an error like 'Load of file /usr/lib/pgsql/plpgsql.so failed :/lib/i686/libc.so.6: version GLIB_2.3 not found (required by /usr/lib/pgsql/plpgsql.so). Kindly guide to solve this issue. all ur helps are highly appreciable.. Thanks in advance, Vijay A chance to meet Aishwarya Rai. Win lucky prizes.
[SQL] createlang problme
Hi All, i'm using RedhatLinux7.2, on top of this i upgrade the Postgres with V7.3.3. After that, i tried to create a language with a createlang command. ERROR: Load of file /usr/local/pgsql/lib/plpgsql.so failed: /usr/local/pgsql/lib/plpgsql.so: un defined symbol: xlateSqlType createlang: language installation failed Can some one help me to come out of this problem, Thanks in advance Vijay _ The hottest things. The coolest deals. http://www.msn.co.in/Shopping/ Get them online! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL]
Hi all, I have few questions to clear my self.. 1. Is that postgres supports distributed database ? _ Talk to Karthikeyan. Watch his stunning feats. http://server1.msn.co.in/sp03/tataracing/index.asp Download images. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] few questions ..?
Hi All, I have few questions, kindly suggest your comments on this.. 1. Is that postgres supports distributed database ? In oracle, by creating Dblink we can communicate the remorte database from the local one. How you could implement this in postgres database. 2. Is there any way to cache the table on memorry ? In oracle, we can use the cache statement at the table creation itself. 3. List out the main tuning parameters in postgres.conf to take care of supporing millions of records. Thanks & Regards Vijay _ Attention NRIs! Banking worries? http://server1.msn.co.in/msnspecials/nriservices/index.asp Get smart tips. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] few questions ..?
Thanks richards ..i will go through that doc.. With Regards Vijay From: Richard Huxton <[EMAIL PROTECTED]> To: "vijaykumar M" <[EMAIL PROTECTED]>, [EMAIL PROTECTED] Subject: Re: [SQL] few questions ..? Date: Thu, 25 Sep 2003 11:07:00 +0100 On Thursday 25 September 2003 09:36, vijaykumar M wrote: > Hi All, > I have few questions, kindly suggest your comments on this.. > > 1. Is that postgres supports distributed database ? > In oracle, by creating Dblink we can communicate the remorte > database from the local one. How you could implement this in postgres > database. See contrib/dblink. I think someone is looking at linking to Oracle too (but I might be wrong about that). > 2. Is there any way to cache the table on memorry ? >In oracle, we can use the cache statement at the table creation > itself. PG uses the operating-system rather than replacing it. If you're using the table regularly it should end up in cache. If you're not using it regularly, you probably didn't want it in cache anyway. > 3. List out the main tuning parameters in postgres.conf to take care > of supporing millions of records. See http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php There are two documents there describing the various config settings and the basics of performance tuning. Millions of records don't need too much tuning even on a standard PC, but the standard config settings are *very* conservative. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] _ Answer simple questions. Win a free honeymoon. http://server1.msn.co.in/sp03/shaadi/index.asp Sail into the sunset! ---(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] Temporary tables
Hi, Try the below steps .. 1. Write one procedure to create tempory table (generic purpose) ** CREATE OR REPLACE FUNCTION SP_CREATE_TEMP_TABLE () RETURNS VARCHAR AS ' DECLARE L_SchemaName name; BEGIN EXECUTE ''CREATE TEMPORARY TABLE temp_table_gen (X VARCHAR);''; SELECT schemaname INTO L_SchemaName FROM pg_stat_user_tables where relname =''temp_table_gen''; RETURN L_SchemaName; END; ' LANGUAGE 'plpgsql'; ** 2. Call the above (generic) procedure to get the temporary table schema name.. by using that schema name ..you can check whether the (real) temporary table is exists or not. ** select into L_SchemaName * from SP_CREATE_TEMP_TABLE(); -- get the schemaname execute ''drop table temp_table_gen;''; -- drop the temptable select schemaname into L_Schema from pg_stat_user_tables where relname=''temp_total_count'' and schemaname =||L_SchemaName||; if (L_Schema is null) then EXECUTE ''CREATE TEMPORARY TABLE temp_total_count (TOTAL_COUNT NUMERIC);''; ELSE EXECUTE ''DELETE FROM temp_total_count;''; END IF; ** I hope this will help u to solve these temporary table issues.. With Regards Vijay From: "George A.J" <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Subject: [SQL] Temporary tables Date: Sat, 27 Sep 2003 06:31:39 -0700 (PDT) hi, I am using postgresql 7.3.2. Is there any function to determine whether a table exists in the database.Or is there any function that returns the current temp schema. I am using a pl/pgsql function that create and drop a temporary table. The procedure run correctly for the first time for each database connection. If I run the same procedure second time in the same connection it produces the error "ERROR: pg_class_aclcheck: relation 219389 not found WARNING: Error occurred while executing PL/pgSQL function testFun WARNING: line 20 at SQL statement " Here is the function - CREATE OR REPLACE FUNCTION testFun( varchar(10) ) RETURNS setof int AS ' DECLARE --Aliases for parameters vSBAcNo ALIAS FOR $1; --local variables vRow RECORD; BEGIN -- create a tempory table to hold the numbers CREATE TABLE tempTable ( testNo int ) ; for vRow IN select Entryno from EntryTable LOOP return next vRow.Entryno; insert into tempTable values( vRow.Entryno); end loop; drop table tempTable; return; END;' LANGUAGE 'plpgsql'; - If i commented the "insert into tempTable values( vRow.Entryno);" line the function works correctly. The problem is the oid of tempTable is kept when the function is first executed. the next execution creates another table with different oid. So the insert fails. I want to check whether the temporary table exist. If exist do not create the temporary table in subsequent calls and do not dorp it. This will solve the problem. When i searched the pg_class i found the temp table name more than once. ie, a temporary table is created for each connection.I cannot distingush the temp tables. But the tables are in different schema. Is there a method to get the current temporary schema? How postgres distinguish this temp tables?.Is there a way to distinguish temporary tables. The entries in pg_class table is same except the schema. When i used the current_schema() function it returns public. There is a lot of functions that uses temporary tables. I think that there is an option when creating temp tables in postgres 7.4 . But no way to use 7.4 now it is a working database. can i write a function to check the existance of the temporary table... please help... jinujose - Do you Yahoo!? The New Yahoo! Shopping - with improved product search _ Keep up with the pace of change. Register for My Tech Ed. http://server1.msn.co.in/sp03/teched/index.asp Realise your potential! ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] how to read bytea contents by using pgsql scripts
Hi All, Is their any way to read the bytea contents by using pgsql script. Thanks & regards Vijay _ BharatMatrimony.com. http://www.bharatmatrimony.com/cgi-bin/bmclicks1.cgi?74 India's premium matrimonial website. ---(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] how to read bytea contents by using pgsql scripts
Hi Christoph, Thanks for ur reply. Let me explain my problem..., In one of my table.. we used bytea datatype for one field.. actaully i want to know the original stored contents (not in byte's) from that field by using pgsql scripts. Thanks in advance.. With regards Vijay From: Christoph Haller <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] (vijaykumar M) CC: [EMAIL PROTECTED] Subject: Re: [SQL] how to read bytea contents by using pgsql scripts Date: Mon, 24 Nov 2003 11:26:02 MET > > Hi All, > Is their any way to read the bytea contents by using pgsql script. > > Thanks & regards > Vijay > > _ > Not sure if that's what you are asking for but you can do e.g. $PGSQLD/bin/psql -d -f ./query2.txt > ./query2.res ./query2.txt has something like select * from bytea_tab ; then you'll find all non-printables in ./query2.res properly escaped as octets as shown in Data Types - Binary Strings. HTH Regards, Christoph ---(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 _ MSN Hotmail now on your Mobile phone. http://server1.msn.co.in/sp03/mobilesms/ Click here. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] how to read bytea contents by using pgsql scripts
Hi Christoph, Yes, U catched my problem, i want to know the original stored contents (not in byte form). With thanks and regards Vijay From: Christoph Haller <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] (vijaykumar M) CC: [EMAIL PROTECTED] Subject: Re: [SQL] how to read bytea contents by using pgsql scripts Date: Mon, 24 Nov 2003 16:12:56 MET > > Hi Christoph, > Thanks for ur reply. > Let me explain my problem..., In one of my table.. we used bytea > datatype for one field.. actaully i want to know the original stored > contents (not in byte's) from that field by using pgsql scripts. > > Thanks in advance.. > > With regards > Vijay > > Sorry, Vijay, but I don't get it. What do you mean by "the original stored contents (not in byte's)" ? Regards, Christoph ---(end of broadcast)--- TIP 8: explain analyze is your friend _ Express your Digital Self. Win fabulous prizes. http://www.msn.co.in/DigitalSelf/ Enter this cool contest. ---(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
[SQL] Pgaccess problem
Hi All,
I am trying to install pgaccess on a redhat 7.2 linux server with
postgres7.4. Everything seems to be alright but when I go to run pgaccess I
get an error message saying
Application initialization failed: no display name and no $DISPLAY
environment v
ariable
Error in startup script: invalid command name "image"
while executing
"image create bitmap dnarw -data {
#define down_arrow_width 15
#define down_arrow_height 15
static char down_arrow_bits[] = {
0x00,0x80,0x00,0x80,0x0..."
(file "/usr/local/pgaccess/main.tcl" line 5)
Can anyone Offer any advice on this problem?
Thanks in advance,
Vijay
_
Stand out from the crowd. Make your own MMS cards. http://msn.migasia.cn/msn
Have some mobile masti!
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] Pgaccess problem on RedhatLinux9
Hi All, Previously i was used RedhatLinux7.2 & Postgres7.4, that time i'm able to use the pgaccess command to view the tables. Presently, i'm using RedhatLinux9 & Postgres7.4, here i'm not able to use the pgaccess command. It is saying "command not found." One thing, i observed was on RedhatLinux7.2,this pgaccess is available at \usr\share\pgsql\pgaccess.this is missing at redhatlinux9. Is there any way to use pgaccess on Redhatlinux9. Thanks in advance, Vijay _ Contact brides & grooms FREE! Only on www.shaadi.com. http://www.shaadi.com/ptnr.php?ptnr=hmltag Register now! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
