Sunday, 24 January 2010
|
| Fast backup/restore Gandalf 16:21:00 |
| | I am looking for a *fast* backup/restore tools for Postgres. I've found
the current used tools pg_dump and pg_restore to be very slow on large
databases (~30-40GB). Restore takes time in the tune of 6 hrs on a Linux, 4
proc, 32 G RAM machine which is not acceptable.
I am using "pg_dump -Fc" to take backup. I understand binary compression
adds to the time, but there are other databases (like DB2) which take much
less time on similar data sizes.
Are there faster tools available?
Thanks.
|
| |  9 answers | Add comment |
Saturday, 23 January 2010
|
| Restore question Steve Doerr 15:41:18 |
| | I've recently had to restore a couple of db's from backup and there were
obsolete employee tables in the dbs.
Now I can't drop or rename this or any other tables to properly load new
ones.
sqltest=# drop table employee
sqltest-#
I used pg_dump on the two dbs, and restored with the following:
psql -f /home/steve/080502sltestdb.out sqltest
Psql is also broken for the next command after one of these failed drops
(when it's showing the - instead of =):
sqltest-# select * from employee;
ERROR: parser: parse error at or near "select"
try again and:
sqltest=# select * from employee;
id | employee_id | name | addr1 | addr2 | addr3 | workphone |
homephone | grade | title_position | salary | salarytype |
exemptcode | gov_id | email | startdate | enddate | notes
Does anyone recognize what's broken here or know what I might be able to
do to fix it?
Thanks for any assistance.
Steve
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
|
| | 2 answer | Add comment |
Friday, 20 November 2009
|
| iplike.so access denied help!! Faqeer ALI 19:59:10 |
| |
i have installed pgsql8.* on fc3 .. it has installed
well and test was successful.
while installing opennms i run the installer
#./installer -disU.. this lead to ca certain error regarding iplike.so
permission denied.
error message
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: could
not access file
"/root/Desktop/downloadable/opennms-1.2.9-1/source/dist/lib/iplike.so":
Permission denied
at
org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
at
org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
at
org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:517)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
at
org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:298)
at org.opennms.install.Installer.updateIplike(Installer.java:1691)
at org.opennms.install.Installer.install(Installer.java:265)
at org.opennms.install.Installer.main(Installer.java:2450)
plz help me out
ALi
_________________________________________________________________
View Athlete s Collections with Live Search
http://sportmaps.live.com/index.html?source=hmemailtaglinenov06&FORM=MGAC01
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 3 answer | Add comment |
Monday, 2 November 2009
|
| Internal function call from C-language function Zoltan Boszormenyi 05:27:13 |
| | Hi,
I need to call date_part() from a C function.
How to do that?
Thanks in advance,
Zolt n B sz rm nyi
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
|
| | 9 answers | Add comment |
Friday, 4 September 2009
|
| suitable driver not found Rajitha M 18:45:44 |
| | The code given below gives an exception as:
a suitable driver not found.
I am not working on a network, it is a simple stand alone PC.
Which SqlDriverr would be suitable,I am using SqlServerDriverfor SQl database 7.0,j2sdk1.41_01 and have installed SqlServerDriver driver sqlserverdriver.jar
and put it in j2sdk1.41_01/jre/lib/ext/sqlserverdriver.jar and then set the classpath
but while running this program i am getting the error like:
classnotfound exception:java.lang.classnotfoundexception:com.microsoft.jdbc.sql
server:SqlServerDriver
sqlexception:java.sql.sqlexception:no suitabledriver
and code is:
import java.sql.*;
import javax.sql.*;
import java.io.*;
import java.lang.*;
public class connect
{ public static void main(String[] args)
{ String url = "jdbc:microsoft:sqlserver://idealab5.cs.uiowa.edu:1433;DatabaseName=Entertainment;";
// here Entertainment is database name
String uid = "admin";
String pw = "";
try { // Load driver class
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
}
catch (java.lang.ClassNotFoundException e) {
System.err.println("ClassNotFoundException: " +e);
}
Connection con = null;
try {
con = DriverManager.getConnection(url,uid,pw);
Statement stmt = con.createStatement();
ResultSet rst = stmt.executeQuery("SELECT * FROM USERS");
//System.out.println("Product Name, Price");
while (rst.next())
{ System.out.println(rst.getString(1));//+","+rst.getDouble(2));
}
}
catch (SQLException ex)
{
System.err.println("SQLException: " + ex);
}
finally
{
if (con != null)
try
{
con.close();
}
catch (SQLException ex)
{
System.err.println("SQLException: " + ex);
}
}
}
}
---------------------------------
Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses. |
| | 6 answers | Add comment |
Thursday, 16 April 2009
|
| How to get user names and passwords Zhaoyong Wu 00:05:01 |
| | Hi,
I created a database and create some users and password. But how to get
these list of users? What are these system table names? Can you tell me?
Thanks.
Zhaoyong Wu
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
|
| | 2 answer | Add comment |
Wednesday, 25 March 2009
|
| ODBC function mapping Peter Eisentraut 11:52:38 |
| | There seems to be a lot of overlap between the odbc.sql file and the
conversion table in convert.c. Do we still need the odbc.sql file at all?
--
Peter Eisentraut peter_e@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
| | Add comment |
Friday, 13 February 2009
|
| solution for error 75 path/file access errpr Amol Bhosale 13:42:05 |
| | I have installed pgAdmin II v1.4.12 on my WinXP computer using
the administrator account.
When I try to connect to a database-server using my own user-id,
I get this run-time error '75': Path/File access error, and pgAdmin
quits. When I log on as administrator I can connect to the server
and everything works fine.
What can I do to run pgAdmin under my own account?
Sincerely
AMOL
---------------------------------
Do you Yahoo!?
Meet the all-new My Yahoo! – Try it today! |
| | 3 answer | Add comment |
Thursday, 18 December 2008
|
| Setting up DSPACE for Postgres access Ashwin Kutty 17:44:32 |
| |
I am currently testing a product by the name of dspace
(http://www.dspace.org) and require installing java since dspace is
completely java based. The problem is that during its installation it
needs to talk to pgsql via jdbc and I am getting the error:
Exception occurred:org.apache.commons.dbcp.DbcpException:
java.sql.SQLException: Driver not found for URL:
jdbc ostgresql://dspace.library.dal.ca:5432/dspace
org.apache.commons.dbcp.DbcpException: java.sql.SQLException: Driver not
found for URL: jdbcostgresql://dspace.library.dal.ca:5432/dspace
I cannot ofcourse go beyond this since they have to talk to each other and
if that doesnt happen nothing else is going to work. In terms of the
system and the env var's; here goes:
RH Linux 9 (kernel 2.4.20)
j2sdk1.4.2_01
dspace - 1.1.1
postgres - 7.3.4
environment var's:
CLASSPATH=/home/dspace/j2sdk1.4.2_01:/dspace/lib/postgresql.jar:/dspace/lib:/usr/local/pgsql/share/java:/dspace/config:/dspace/lib:/dspace/lib/activation.jar:/dspace/lib/commons-cli.jar:/dspace/lib/commons-collections.jar:/dspace/lib/commons-dbcp.jar:/dspace/lib/commons-pool.jar:/dspace/lib/cos.jar:/dspace/lib/dspace.jar:/dspace/lib/handle.jar:/dspace/lib/jena.jar:/dspace/lib/log4j.jar:/dspace/lib/lucene.jar:/dspace/lib/mail.jar:/dspace/lib/oaicat.jar:/dspace/lib/oro.jar:/dspace/lib/servlet.jar:/dspace/lib/xalan.jar:/dspace/lib/xerces.jar:/home/dspace/j2sdk1.4.2_01/lib:/home/dspace/j2sdk1.4.2_01:/home/dspace/j2sdk1.4.2_01/jre/lib:/home/dspace/j2sdk1.4.2_01/jre/javaws:/home/dspace/dspace-1.1.1-source/build/classes:/usr/local/pgsql/share/java:/usr/local/pgsql/share/java/postgresql.jar
PGDATA=/usr/local/pgsql/data
PGSQL_HOME=/usr/local/pgsql/bin
JAVA_HOME=/home/dspace/j2sdk1.4.2_01
PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/home/dspace/apache-ant-1.5.4/bin:/usr/local/pgsql/share/java:/usr/local/pgsql/bin:/home/dspace/j2sdk1.4.2_01:/home/dspace/j2sdk1.4.2_01/bin:/home/dspace/bin
JAVA_OPTS=-Xmx512M -Xms64M -Dfile.encoding=UTF-8
I do realise that support from the developers from DSPACE should be
available but it has not been so far. As long as I get JDBC to work with
PGSQL, it would at the very least get me started.
Any help would be greatly appreciated.
Thanks.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
|
| | 11 answers | Add comment |
Wednesday, 22 October 2008
|
| plpgsql - Insert from a record variable? Phil Endecott 14:20:15 |
| | Dear Postgresql experts,
I'm writing a simple plpgsql function that selects a row from a table, modifies it slightly, and inserts the modified version. This sounds simple, but I can't find how to do the insert in a simple generic fashion: although SELECT has a form that puts the result in a record variable, INSERT doesn't seem to have anything similar.
What I'd like to be able to write is something like this:
DECLARE
R RECORD;
BEGIN
SELECT * INTO R FROM TABLE WHERE id=n;
R.something := x;
INSERT INTO TABLE R;
END
But as far as I can see the only syntax that is allowed for INSERT is the normal INSERT INTO TABLE (x,y,z) VALUES (a,b,c). To achieve what I want to do I'd need to iterate over the fields of the record (how?) and build up the string of the INSERT query.
It seems odd that SELECT can use a record variable but INSERT can't, so I wonder if I have missed something. Any suggestions?
Thanks in advance for any help anyone can offer.
Regards,
--Phil.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
|
| | 8 answers | Add comment |
Wednesday, 10 September 2008
|
| calling a pl/pgsql function with array in argument Ruben Vivas 12:33:45 |
| | Hello, i am tryin to pass an array like argument to a pl/pgsql function but
i can not find the correct data type in te function.
i am using php.
ex: $dato[][] is the array using php, and i need call:
1. $res = fn_vea ($dato); ?????
2. in pl/pgsql CREATE FUNCTION fn_vea (integer [][]) RETURNS varchar (25) AS
.........
does it work??
thank you
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
|
| | Add comment |
Monday, 1 September 2008
|
| PgDatabase, on heap construction results in SIGSEGV. Savage 06:32:09 |
| | I am having a problem constructing a PgDatabase object. If I create
the object on the stack (as a var local to a function) then it
constructs and connects to the DB ok. But, if I create and construct
the object on the heap, a SIGSEGV occurs within libpq++.so.4.
Has anyone got any ideas what could be causing this? I have only been
programming under linux for a month or so at this stage, but I've a
lot of Win32 experience. Any pointers to things that a Win32 to Linux
newbie would not be aware of would be much appreciated.
Thanks,
John
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
|
| | 1 answer | Add comment |
Tuesday, 19 August 2008
|
| Backup and restore through JDBC Marlon Petry 14:35:25 |
| | Hello List
I am trying to develop, a API to carry through backup and restore through
JDBC.
I think that the best form is to use JNI.
Some Suggestion?
|
| | 15 answers | Add comment |
Tuesday, 22 July 2008
|
| Recordset problem Jean-Baptiste ESTIVAL 21:38:53 |
| | Hello,
I can't use recordset.addnew in VB6 with postgreodbc
the current connection is OK, the recordset is opened as dynamic, like the exemple given on the odbc site.
So, if anyone have a solution,
Regards,
JiB
|
| | 14 answers | Add comment |
Tuesday, 1 July 2008
|
| Is there an ODBC driver available for Windows 2003 64-bit Regina Obe 09:31:32 |
| | I'm moivng over SQL Server 2000 32-bit databases over to SQL Server 2005 64-bit and ran into a small problem.
In the old setup, I had a linked server configuration to a Postgresql server which used the 32-bit driver. In the new setup, the SQL Server 2005 64-bit can't see the 32-bit drivers (unless I run in 32-bit mode) and I don't think its possible for it to since its runing in 64-bit SQL Server. So I'm guessing I need to get a 64-bit PostGreSQL ODBC driver, somehow get the pgsql .NET driver to be seen as an option, or possibly the 32-bit.
Thanks,
Regina
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally
privileged and/or exempt from disclosure pursuant to Massachusetts
law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and
delete the material from any computer.
|
| | 1 answer | Add comment |
Saturday, 21 June 2008
|
| phpPgAdmin on Debian Sarge John Hedge 08:34:11 |
| | Hi,
Can someone point me at a 'howto' install phpPgAdmin from the point where
I've apt-get install phpPgAdmin and dependencies (Apache etc.), please?
All I get for http://192.168.3.22/phpPgAdmin/index.php is:
Not Found
The requested URL /phpPgAdmin/index.php was not found on this server.
------------------------------
Apache/2.0.55 (Debian) Server at 192.168.3.22 Port 80
TIA
John
|
| | 1 answer | Add comment |
Tuesday, 17 June 2008
|
| error codes in postgresql Sandhya 19:32:12 |
| |
Hi,
I want to know ,if there is any way to return error codes/numbers as a return value from the postgresql functions.
For ex:
If i am using my connection function say,
DbConnect(dname,uname,.........)
{
//here i use postgres connection function
pg_conn *conn;
conninfo="dbname=example(dname) user=postgres(uname)";
conn=PQConnectdb(conninfo)
//After connecting i need to create a table..and if the table exists
PQerrormessage(conn) will return error message...
But what i need is ..
Is there any other way of returning error number to my main program.
Can you please tell me how to handle these errors in my application.
I referred posgresql site..I didn't get any clear idea of it.
Any help please......
Thank you...
Regards,
Sandhya R
|
| | 7 answers | Add comment |
Saturday, 24 May 2008
|
| Really stupid question(?) Bear Giles 12:05:58 |
| | I've been giving a lot of thought to some of the questions raised
by my SSL patch, and have both a conclusion and a really stupid
question.
First, the conclusion is that what I'm working on is "secure sessions."
As I mentioned before, that's not just encryption (e.g., SSH tunnels),
but the combination of confidentiality (encryption), message integrity
and endpoint authentication. This is what people think you mean when
you say an application "supports" Kerberos or SSL, and it's what's
required for really sensitive information.
(E.g., nobody cares that the data was encrypted if the confidential
information supporting a search warrant went to the bad guys instead
of directly to the central police database. The snitch is still
dead, and the evidence destroyed.)
The latest SSL patches will be out by this weekend, and I hope to
add GSS-API (which includes Kerberos 5) soon afterwards. Both will
publish their endpoint authentication information (X509 structure
and strings containing subject and issuer distinguished names, string
containing Kerberos principal name), and the HBA code can then use
this information for PostgreSQL authentication.
...
The really stupid question refers to some of the hardcoded fallback
values in this code. The reason for having hardcoded values is to
prevent "downgrade" attacks - you don't want to casually override the
DBA, but you also don't want to make it easy for a knowledgeable
attacker to fatally compromise the system in a way that your average
DBA couldn't catch.
But the problem is that knowledgeable security administrators can
replace the common hardcoded values with their own. How do you allow
this to be easily done?
One possibility that occured to me was that dynamic libraries would
handle this nicely. There's even some support for dynamic libraries
in the user-defined functions, so this wouldn't be a totally
unprecedented idea.
But this would be a new way of using dynamic libraries. Is this
something everyone is comfortable with, or is it problematic for
some reason? Or is this premature - maybe the first release should
just use hardcoded values with a note to contact individuals if
there's an interest in a dynamic library approach?
Bear
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
|
| | 8 answers | Add comment |
Monday, 5 May 2008
|
| Multilingual Support Nandha Kumar 09:32:22 |
| | hi,
Does PostgreSQL support Multilingual data?
Is it possible to have rows in different languages like japanese etc?
nandha kumar & prasad
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
|
| | 2 answer | Add comment |
Tuesday, 6 November 2007
|
| updating Angelo Rigo 21:33:48 |
| | Hi
I am working on an update php script, i have a list of the records and at
the bottom of the script i do write the id number
on the id field i want to change and the others fields values, i have to
write it, i want to select with a radio button and to get the fields at
the bottom receiving the values of that line i do select how can i do that?
Below is my script:
//part one: list and update form.
<?php
$db = pg_connect("dbname=db user=user");
$query = "SELECT * FROM thetable";
$result = pg_exec($db, $query);
if (!$result) {printf ("ERROR"); exit;}
$numrows = pg_numrows($result);
$row=0;
printf ("<table border=1>
");
printf ("<tr bgcolor='#66CCFF'><td><b>ID</b></td><td><b>Cidade</b></td><td><b>Estado</b></td><td><b>Local</b></td><td><b>Dia</b></td><td><b>MГЄs</b></td><td><b>HorГЎrio</b></td><td><b>Evento</b></td></tr>");
do
{
$myrow = pg_fetch_array ($result,$row);
printf ("<tr bgcolor='$bgcolor'><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>
",$myrow[id],$myrow[cidade], $myrow[estado], $myrow[localidade],$myrow[dia],
$myrow[mes], $myrow[horario],$myrow[evento]);
$row++;
}
while ($row < $numrows);
printf ("</table><br>
");
pg_close($db);
?>
<form action="atual.php" method="post">
<TABLE>
<TR><TD>ID to modificar :</TD>
<TD><input type="text" name="id" size="4" length="4" value="id"></TD>
</TR>
<TR><TD>Cidade : </TD>
<TD><input type="text" name="cidade" size="80" length="80" value=""></TD>
</TR>
<TR><TD>Estado :</TD>
<TD><input type="text" name="estado" size="80" length="80" value=""></TD>
</TR>
<TR>
<TD>Local :</TD>
<TD><input type="text" name="local" size="80" length="80" value=""></TD>
</TR>
<TR>
<TD>Dia : </TD>
<TD><input type="text" name="data" size="80" length="80" value=""></TD>
</TR>
<TR>
<TD>MГЄs :</TD>
<TD><input type="text" name="data" size="80" length="80" value=""></TD>
</TR>
<TR>
<TD>HorГЎrio :</TD>
<TD><input type="text" name="horario" size="80" length="80" value=""></TD>
</TR>
<TR>
<TD>Evento :</TD>
<TD><input type="text" name="evento" size="80" length="80" value=""></TD>
</TR>
<TR>
<TD><input type="submit" name="submit" value="Atualizar"></TD>
<TD><input type="reset" name="reset" value="Limpar"></TD>
</TR>
</TABLE>
// atual .php
<?php
$db = pg_connect("dbname=db user=puser");
$query = "UPDATE thetable SET $id='$id', cidade='$cidade', estado='$estado',
localidade='$localidade', dia='$dia', mes='$mes', horario='$horario', evento='$evento'
WHERE id='$id'";
$result = pg_exec($db, $query);
if (!$result) {printf ("ERROR"); exit;}
printf ("these values were updated: %s %s %s %s %s %s %s %s", $id, $cidade,
$estado, $localidade, $dia, $mes, $horario, $evento);
pg_close($db);
?>
________________________________________
A busca mais veloz e precisa da internet. Acesse agora: http://www.zoom.com.br.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
|
| | 2 answer | Add comment |
Saturday, 27 October 2007
|
| build error Darko Prenosil 00:35:30 |
| | Yesterdays snapshot fails to build on my NT:
gcc -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declaratio
ns -I../../src/port -I../../src/include -I./src/include/port/win32 -DEXEC_BA
CKEND "-I../../src/include/port/win32" -c -o path.o path.c
gcc -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declaratio
ns -I../../src/port -I../../src/include -I./src/include/port/win32 -DEXEC_BA
CKEND "-I../../src/include/port/win32" -c -o pipe.o pipe.c
pipe.c: In function `pgpipe':
pipe.c:32: warning: implicit declaration of function `ereport'
pipe.c:32: `LOG' undeclared (first use in this function)
pipe.c:32: (Each undeclared identifier is reported only once
pipe.c:32: for each function it appears in.)
pipe.c:32: warning: implicit declaration of function `errmsg_internal'
make[2]: *** [pipe.o] Error 1
make[2]: Leaving directory `/d/postgresql-snapshot/src/port'
make[1]: *** [all] Error 2
make[1]: Leaving directory `/d/postgresql-snapshot/src'
make: *** [all] Error 2
Putting
#include "utils/elog.h"
in the beginning of the pipe.c solves the problem
However I did the build because I was trying to compile tsearch2, and it
fails too:
dict_ispell.o(.text+0x31b):dict_ispell.c: undefined reference to
`pg_strcasecmp'
dict_ispell.o(.text+0x420):dict_ispell.c: undefined reference to
`pg_strcasecmp'
dict_ispell.o(.text+0x500):dict_ispell.c: undefined reference to
`pg_strcasecmp'
wparser_def.o(.text+0xc83):wparser_def.c: undefined reference to
`pg_strcasecmp'
wparser_def.o(.text+0xcd9):wparser_def.c: undefined reference to
`pg_strcasecmp'
wparser_def.o(.text+0xd02):wparser_def.c: more undefined references to
`pg_strcasecmp' follow
ispell/SUBSYS.o(.text+0xbd2):spell.c: undefined reference to
`pg_strncasecmp'
ispell/SUBSYS.o(.text+0xbfc):spell.c: undefined reference to
`pg_strncasecmp'
ispell/SUBSYS.o(.text+0xc15):spell.c: undefined reference to
`pg_strncasecmp'
ispell/SUBSYS.o(.text+0xc2e):spell.c: undefined reference to
`pg_strncasecmp'
c:\mingw\bin\dllwrap.exe: c:\mingw\bin\gcc exited with status 1
make: *** [libtsearch2.a] Error 1
Too late tonight to dig more...
Regards !
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
|
| | 6 answers | Add comment |
Monday, 24 September 2007
|
| Problem with the use of Array and Loop Benjamin RICHARD 23:43:45 |
| | Hi,
I would like to read each value of an array.
To do this i create the following function which return me a parse error
near '[' (in the Raise notice line)
What mistake did i do ?
And maybe there is a better way to do this in Postgre
CREATE FUNCTION array_int(_int4) RETURNS int4 AS 'DECLARE
start_char INTEGER DEFAULT 4;
len_char INTEGER;
array_count INTEGER;
val_tmp VARCHAR;
tmp INTEGER;
BEGIN
SELECT INTO val_tmp array_dims( $1 ) ; --get the length of the array =>
[x:y]
SELECT INTO len_char ( position('']'' in val_tmp) - start_char ) ; --get the
length of y
SELECT INTO array_count substring(val_tmp from start_char for len_char)
; --get the value of y
FOR tmp IN 1..(array_count) LOOP
RAISE NOTICE ''current value = %'' , $1[tmp] ;
END LOOP;
RETURN array_count ;
END;
-------------------------------------------------------------------
Try :
select array_int('{5,3,6,9,7,5,10,5,87,45,12,65,98,21,65,21}') ;
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
|
| | 2 answer | Add comment |
|
