Also can you explain if I built Postgres from source on one platform lets
say RHEL_6 and deployed its artifacts like its binaries, libs and share on
a CentOS and tried building extensions against Postgres on CentOS are there
any dangers of doing that?
On Wed, Feb 1, 2017 at 8:34 PM, postgres user
Hi,
I am wondering about this question for a while with no definite answer to
it, can someone explain me in detail to clear me out on the following
question :
What can go wrong or is it acceptable if I build Postgres from source with
let's say GCC 4.x.y and some specific CFLAGS, CPPFLAGS and
Hi,
If I have the Postgresql server installed on my machine i.e I have all the
bins, libs and share directories of the Postgresql and I have the libs and
sql's installed for one of the contrib extensions lets say "chkpass", how
does one go about testing this extension exhaustively on the server?
Hi,
I want to configure my PostgreSQL installation in such a manner such that
the contrib modules of the PostgreSQL distribution are stored in a specific
directory and they should use the PGXS extensions management system
supported by Postgres, as they are currently packaged along with Postgres
The already installed Postgres edition was built using the same
installation procedure as mentioned in the docs, but without the use of
--with-perl flag. the point I ask the question is because I want to install
PL/Perl as a separate extension as one does with PostGIS and not along with
Postgres
Hi,
I am trying to build PL/Perl procedural language to my PostgreSQL server
installation but I want to do it without passing --with-perl flag to the
configure script of PostgreSQL. It would also help if someone can explain
the meaning of --with-perl flag in a detailed fashion altogether as it
Hi,
According to the docs, the DEFAULT keyword lets you explicitly insert a
field's default value in an INSERT statement.
From a db function, I'd like to force the use of default when an input
parameter is null. This syntax looks correct but fails to compile.
Any suggestions?
INSERT INTO
Thanks for the previous posts. I am in fact running 9.0 'nix and am unable
to find a way to embed DEFAULT within an expression.
I can always rewrite the function to call EXECUTE but that's not a very good
solution in this case because many of our db functions are code generated.
On Tue, Aug 9,
Good idea but you're right, if the default value is an expression such as a
the next serial value, it will need to be executed first. If Execute
doesn't return the interpreted value, I don't know of a way to make it
work..
On Tue, Aug 9, 2011 at 5:35 PM, Diego Augusto Molina
On Sat, Dec 19, 2009 at 6:56 AM, Merlin Moncure mmonc...@gmail.com wrote:
On Sat, Dec 19, 2009 at 1:05 AM, Postgres User
postgres.develo...@gmail.com wrote:
BEGIN
SELECT array_agg(category_id) INTO cat_list FROM (
WITH RECURSIVE subcategory AS
(
SELECT
Hi,
I have a function that returns a refcursor that I need to call from a
second function. In the second function, I'd like to read a column
value from each row. However, I'm having a problem accessing the rows
of the refcursor.
Can anyone point me to a working example of how to pull this off?
Great call, someone did hose the data. Oddly enough the circular
reference caused no problem when running the stand alone recursive SQL
(with clause).
On Sat, Dec 19, 2009 at 10:41 AM, Merlin Moncure mmonc...@gmail.com wrote:
On Sat, Dec 19, 2009 at 1:30 PM, Postgres User
postgres.develo
Hi,
I'm trying to write a very simple function statement to select a
single integer field from a table and save it into an int array. For
some reason I can't seem to find the correct syntax:
CREATE TABLE sample (
id integer
);
and then within a function:
my_array int[];
my_array = SELECT
On Fri, Dec 18, 2009 at 9:53 PM, Merlin Moncure mmonc...@gmail.com wrote:
On Fri, Dec 18, 2009 at 11:35 PM, Pavel Stehule pavel.steh...@gmail.com
wrote:
2009/12/19 Postgres User postgres.develo...@gmail.com:
Hi,
I'm trying to write a very simple function statement to select a
single
Hi,
I've encountered a serious Postgres bug which apparently has a fix
that hasn't been released in a patch. How or where can I look up its
status?
This is a serious bug- it basically renders RETURN QUERY useless in
any environment where you don't have the option of dropping and
re-creating a
Has anyone seen any performance metrics comparing the Opteron 6-core
Istanbul class processor aginst the 4-core Xeon e5500 series
processor, esp running a dataase? Or has anyone compared these 2
processor options before ordering a Postgres server?
The 6 core processor should offer a clear edge
Has anyone seen any performance metrics comparing the Opteron 6-core
Istanbul class processor aginst the 4-core Xeon e5500 series
processor, esp running a dataase? Or has anyone compared these 2
processor options before ordering a Postgres server?
The 6 core processor should offer a clear edge
Browsing the docs last night, I realized that I've never taken
advantage of Postgres' powerful composite types. But a question came
to mind- in what scenarios should you use a composite type in a table
structure? That is, I can see the benefits of a composiite type
insofar as it essentially lets
Browsing the docs last night, I realized that I've never taken
advantage of Postgres' powerful composite types. But a question came
to mind- in what scenarios should you use a composite type in a table
structure? That is, I can see the benefits of a composiite type
insofar as it essentially lets
EMS SQL Manager has a visual query builder, but it's a commerical
product, ie it aint free.
On Tue, Oct 6, 2009 at 12:47 PM, pere roca pero...@gmail.com wrote:
hi,
some nice tool over there to let non-SQL knowing people to construct their
queries? I'm using pgAdmin III but I know some SQL.
Hi,
I'm trying to write an INSERT INTO statement that will use a DEFAULT
value when an input parameter is null.
Here's the function that fails to compile. I tried replacing Coalesce
with a Case statement but that fails as well. Note that if you
replace the condition with a simple 'Default' it
I'm trying to write an INSERT INTO statement that will use a DEFAULT
value when an input parameter is null.
Neither of my 2 methods are pretty.
1) Use a trigger.
2) Grab and cast the default value from the information_schema.columns
view and plug it in.
Another option is to build your
I'm trying to write an INSERT INTO statement that will use a DEFAULT
value when an input parameter is null.
Neither of my 2 methods are pretty.
1) Use a trigger.
2) Grab and cast the default value from the information_schema.columns
view and plug it in.
Another option is to build
Hi,
I have a simple function that returns a set of rows:
CREATE OR REPLACE FUNCTION foo()
RETURNS SETOF record AS
$$
BEGIN
RETURN QUERY SELECT * FROM people WHERE last_name = 'jones';
END
$$
LANGUAGE 'plpgsql'
In a separate function, I call the function and store the results in a
temp
In the docs, I see repeated references to $user in the postgresql.conf
schema search_path setting such as:
search_path = '$user,public'
But I don't see any info on the meaning of '$user' here. Is $user
some kind of variable within postgresql.conf that refers to the
current user? Can it be
Thanks for the link, I wasn't reading the right page(s) in the documentation.
On Mon, Jul 6, 2009 at 12:19 PM, Tom Lanet...@sss.pgh.pa.us wrote:
Postgres User postgres.develo...@gmail.com writes:
In the docs, I see repeated references to $user in the postgresql.conf
schema search_path setting
Does anyone have a recommendation for maintaining user permissions on
a changing database? The lack of an option to grant specific rights
to all objects of a given type within a Postgres db obviously places
the burden on the administrator to keep roles updated as objects are
added and dropped
Hi,
I'm writing a small ORM tool and have written a number of queries to
retrieve table metadata. One piece of data that I'm having trouble
hunting down is the size of a CHAR field. For example, one table has
a 'user_id' column of type CHAR(36). But when I look at the
pg_attribute and pg_type
Hi,
I'd writing a query against a function (pg_proc) that contains 2
fields of an array type. Ideally, I'd like to select 1 row from the
table, but return a query row for each item in the array.
For example, if one row contains the array {a, b, c}
I'd like the query to return 3 rows, one for
Thanks for all the replies. I'm going to post the results of using
the recommended approach in another thread.
On Fri, May 29, 2009 at 1:18 PM, Adam Ruth adamr...@mac.com wrote:
Good point, I should have specified 8.3.7.
Just one more reason to anxiously anticipate upgrading to 8.4.
On
Based on replies to another post (recommending use of
'generate_series'), I was able to write the following query that
returns all paramters of a given function.
Only one interesting thing to note- in order to return the proper
argument type, I had to use
proargtypes[i - 1] when I expected
Hi,
I have a database that was created with SQL-ASCII encoding
(unfortunately). I ran pg_restore to load the struct and data into a
new database with UTF-8 encoding but no surprise- I'm seeing this
error for a number of tables:
pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte
Hi,
Does anyone know the maximum number of elements allowed in a
one-dimensional array? It looks like my script may add at least a
couple million, not sure if it will hit a limit...
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
Has anyone written a function that scripts out all the functions in a
database as full SQL statements (Create Function.)
I found the below SQL will return all the fields needed to build a SQL
statement, but it would take some work to combine the field values
correctly to get the right format.
[EMAIL PROTECTED] wrote:
On Sun, May 11, 2008 at 06:12:40AM -0700, Postgres User wrote:
Has anyone written a function that scripts out all the functions in a
database as full SQL statements (Create Function.)
I found the below SQL will return all the fields needed to build a SQL
:37AM -0700, Postgres User wrote:
Unfortunately I didn't see a way to tell pg_dump to dump only objects
of a specific type, like functions or sequences. It requires
additional coding to parse the output and that's less than ideal...
hmmm .. additional coding seems a bit too much for a simple
if a function includes this SQL:
Update Table1 Set field_1 = 'ab';
Insert Table2(field_2) VALUES('cd');
and I create an update trigger on Table1:
Create Trigger Table1_Update AFTER Update
On Table1 FOR EACH ROW:
Select * From Table2
will the Select statement in the trigger see
Question answered- needed to move Insert statement before Update in
main function.
On Fri, Mar 21, 2008 at 6:42 PM, Postgres User
[EMAIL PROTECTED] wrote:
if a function includes this SQL:
Update Table1 Set field_1 = 'ab';
Insert Table2(field_2) VALUES('cd');
and I create an update
be ignored by the optimizer-
or am i missing something
On Feb 19, 2008 9:45 PM, Tom Lane [EMAIL PROTECTED] wrote:
Postgres User [EMAIL PROTECTED] writes:
Yes that works, but the whole point of the exercise is replace many OR
statements with 1 regex expression. So it's not what I'm looking
with a
condition equivalent to many OR conditions when using basic comparison
operators:
select * from table1
where name ~ '.*' '^Smith$' |^Jones$':
And this works very well- except for the seq scan instead of an index scan
On Feb 20, 2008 2:31 AM, Tino Wildenhain [EMAIL PROTECTED] wrote:
Postgres User
as it
ignores WHERE 1 = 1
On Wed, Feb 20, 2008 at 8:31 AM, Tom Lane [EMAIL PROTECTED] wrote:
Postgres User [EMAIL PROTECTED] writes:
My users are developers and the goal was to accept a simple
comma-delimited list of string values as a function's input parameter.
The function would then parse
I'm running a simple query on 8.2. With this syntax, Explain indicate
that the index is scanned:
select * from eod where name = 'AA'
However, when I change the query to use simple regex:
select * from eod where name ~ 'AA'
now Explain indicates a seq scan:
Index Scan using equity_eod_symbol_idx
, Erik Jones [EMAIL PROTECTED] wrote:
On Feb 19, 2008, at 9:32 PM, Postgres User wrote:
I'm running a simple query on 8.2. With this syntax, Explain indicate
that the index is scanned:
select * from eod where name = 'AA'
However, when I change the query to use simple regex:
select
where fielda ~ p_param
(where p_param is the input parameter)
On Feb 19, 2008 9:34 PM, Chris [EMAIL PROTECTED] wrote:
Postgres User wrote:
Yes that works, but the whole point of the exercise is replace many OR
statements with 1 regex expression. So it's not what I'm looking for.
Why do you
Yes that works, but the whole point of the exercise is replace many OR
statements with 1 regex expression. So it's not what I'm looking for.
On Feb 19, 2008 9:16 PM, Chris [EMAIL PROTECTED] wrote:
Postgres User wrote:
Thanks, my dumb mistake.
I need to perform the equivalent of a WHERE
* from table where name in (name)
postgres does a row scan on the above sql. too slow.
On Feb 19, 2008 9:34 PM, Chris [EMAIL PROTECTED] wrote:
Postgres User wrote:
Yes that works, but the whole point of the exercise is replace many OR
statements with 1 regex expression. So it's not what
doh! tom, let me know if you decide to hack out a fix for this one of
these nights ;)
thanks for your help.
On Feb 19, 2008 9:45 PM, Tom Lane [EMAIL PROTECTED] wrote:
Postgres User [EMAIL PROTECTED] writes:
Yes that works, but the whole point of the exercise is replace many OR
statements
Hopefully someone can test on Windows to validate.
On Nov 30, 2007 8:25 AM, Martijn van Oosterhout [EMAIL PROTECTED] wrote:
On Fri, Nov 30, 2007 at 08:20:30AM -0800, Postgres User wrote:
tom- did you test this on wndows? you can ignore the namespace- i'm
using it consistently but removed from
van Oosterhout [EMAIL PROTECTED] writes:
On Fri, Nov 30, 2007 at 12:23:31AM -0800, Postgres User wrote:
However, I have found that my record variable is not assigned proper
field-level datatypes. As a result, I'm unable to write basic math
calcs in pg/sql without a lot of typecasting
According to the docs, record variables take on the actual row
structure of the row they are assigned during a SELECT or FOR
command.
However, I have found that my record variable is not assigned proper
field-level datatypes. As a result, I'm unable to write basic math
calcs in pg/sql without a
Williamson
Sent: Thu 11/29/2007 10:37 PM
To: Postgres User; pgsql-general
Subject: Re: [GENERAL] Simple math statement - problem
The question:
How can I write statements that returns a decimal?
billing=# select 1/100;
?column?
--
0
(1 row)
As you said ...
So
';
On Nov 30, 2007 12:31 PM, Postgres User [EMAIL PROTECTED] wrote:
Sure, I'd be glad to provide any additional code or info that I can.
This issue took me quite awhile to track down from the 200-line
function that was breaking.
When I execute the function defined above, here's the output:
select
8.2 / Windows (a development-only pc)
On Nov 30, 2007 12:50 AM, Martijn van Oosterhout [EMAIL PROTECTED] wrote:
On Fri, Nov 30, 2007 at 12:23:31AM -0800, Postgres User wrote:
However, I have found that my record variable is not assigned proper
field-level datatypes. As a result, I'm
I have a large function that's doing a number of calcs. The final
return value is wrong for a simple reason: any division statement
where the numerator is less than the denominator is returning a zero.
Each of these statements return a 0, even when properly cast:
select 1/100
select Cast(1 /
Hi,
I'm new to Regex in Postgres. Can someone give me a quick pointer on
how I'd SELECT the substring between 'name'and '/name' in
a field?
Sample field data:
address city here nameRogers, Jim/name zip code place
and I'd like the SELECT to return only:
Rogers, Jim
Thanks!
Yes, I read the manual. I think I had a problem because of the
special chars ( / ) that I'm trying to search for... Still looking
for the right syntax.
On 8/23/07, Michael Glaesemann [EMAIL PROTECTED] wrote:
On Aug 23, 2007, at 19:33 , Postgres User wrote:
I'm new to Regex in Postgres
Yes, I read the manual. I think I had a problem because of the
special chars ( / ) that I'm trying to search for... Still looking
for the right syntax.
Why don't you show us what you've tried and the errors you're
getting? That way we can help you figure out what you're doing wrong
I'm new to Regex in Postgres. Can someone give me a quick pointer on
how I'd SELECT the substring between 'name'and '/name' in
a field?
Sample field data:
address city here nameRogers, Jim/name zip code place
and I'd like the SELECT to return only:
Rogers, Jim
Thanks!
On 8/23/07, Michael Glaesemann [EMAIL PROTECTED] wrote:
On Aug 23, 2007, at 20:01 , Postgres User wrote:
Yes, I read the manual. I think I had a problem because of the
special chars ( / ) that I'm trying to search for... Still
looking
for the right syntax.
Why don't you show us
Hi,
I have a quasi materialized view that's maintained by INS, UPD, and
DEL triggers on several child tables.
The tables involved have different structures, but I needed a single
view for selecting records based on a few common fields. This
approach is much faster than querying the separate
Hi,
I have a quasi materialized view that's maintained by INS, UPD, and
DEL triggers on several child tables.
The tables involved have different structures, but I needed a single
view for selecting records based on a few common fields. This
approach is much faster than querying the separate
I have a table of around 6,000 places in the world. Everytime my
server receives a ping, I'm grabbing the content of an article from an
RSS feed. Then I search the article for the presence of any the 6000
terms.
A typical article is around 1200 words.
I don't need to save the article in a
A basic question about raising exceptions in Postgres:
If Function A calls Function B
and Func B raises an exception, will the exception roll back the
transaction in Func A by default? Or do I need to trap and re-raise
the exception in Func A?
Thanks.
---(end of
Your query won't work because there is no single Price associated with
a range of dates. It doesn't make sense.
Do you mean to select AVG(Price)?
On 6/28/07, Bauhaus [EMAIL PROTECTED] wrote:
Hello,
I'm an Access/SQL novice and I have an sql problem:
I have the following table Price:
FuelID
/30/07, Wiebe Cazemier [EMAIL PROTECTED] wrote:
On Saturday 30 June 2007 23:14, Postgres User wrote:
A basic question about raising exceptions in Postgres:
If Function A calls Function B
and Func B raises an exception, will the exception roll back the
transaction in Func A by default
Where func A, B, C, and D all update the db. If an EXCEPTION is raised
in func D(), will all the transactions in the other children be rolled
back?
Or do I need to add code to enable this?
On 6/30/07, Postgres User [EMAIL PROTECTED] wrote:
How about this scenario:
func A()
begin
x = func
You'll need to create a custom function in Postgres to support this,
which is fairly easy. It's been done before- do a search on Google:
http://www.hclausen.net/psql.php
On 5 Apr 2007 01:27:15 -0700, marcel.beutner [EMAIL PROTECTED] wrote:
Hello,
Thanks a lot for your answers! But I don't
Andrus,
As a C# developer myself, I'd recommend learning pl/pg sql for
writring stored procs. It's designed specifically for the kinds of
iterations and other operations you need when hanlding a recordset.
It's nothing like transact-sql of SQL Server.
If you must have full C# integration, then
Marcel,
A sequence represents a unique identifier. You can call the function
'nextval' to get the next unique value in the sequence. See related
functions here:
http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html
In this code, I get the next sequence, insert it into a
My guess is that when you insert into Cities, the sequence field is
incremented one time (=2), and when the Rule is executed to insert
into Capital, the sequence is incremented a second time (=3). As a
result of these 2 operations, the sequence is incremented 2 times.
I don't know anything
I recently heard from Josh Berkus that at least one major CMS
application uses Postgres to store entire HTML pages (including image
files) in order to support full versioning.
As a general rule, I prefer not to store BLOBS in a DB- I'd rather
leave the BLOB in the file system and let the db save
Oleg,
This looks like a great module, do you have a pointer to it in English?
If can send this module to me as a compressed file, I'll take the time
to post it on PgFoundry as a new project that everyone can easily
access and download.
Paul- if you go with the lower() edits route, be sure to
Is there a way to grant INSERT and UPDATE permissions on all the
tables in a database? I don't want to type-in every table name...
---(end of broadcast)---
TIP 6: explain analyze is your friend
Hi,
I'm using this code to increment a counter table:
IF Exists (SELECT keyfld FROM counter_tbl WHERE key_fld = 'key_val') THEN
UPDATE counter_tbl SET counter_fld = counter_fld + 1
WHERE key_fld = 'key_val';
ELSE
INSERT INTO counter_tbl(key_fld, counter_fld) VALUES('key_val',
THEN
RETURN;
END IF;
BEGIN
INSERT INTO db(a,b) VALUES (key, data);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;
On 3/4/07, Berend Tober [EMAIL PROTECTED] wrote:
Postgres
Hi,
I'm opening a refcursor in Postgres to return a dataset to the client.
However, before returning the cursor, I'd like to iterate thru the
rows. Here's the code:
DECLARE
ref_entry refcursor;
rec record;
i integer = 0;
v_list varchar = '';
BEGIN
OPEN ref_entry FOR
PROTECTED] wrote:
Postgres User [EMAIL PROTECTED] writes:
Is there any way to 'rewind' the cursor to the first row?
plpgsql doesn't have any command for that (though I think someone is
working on improving its cursor command set). You should be able to
work around it by EXECUTE'ing a MOVE BACKWARD ALL
If you need to return a record to another function or client program,
you can always use this sytnax:
SELECT var_a::integer, var_b::integer, var_c::integer... etc
where var_a, var_b, etc and local variables that hold your calculated values.
You dont need to create a local record structure-
';
Any other thoughts?
On 3/1/07, Tom Lane [EMAIL PROTECTED] wrote:
Postgres User [EMAIL PROTECTED] writes:
So I added the following code:
before opening cursor ref_entry = 'c_entry';
after looping thru cursor MOVE Backward All In c_entry;
You have to use EXECUTE for the latter
mytable mt into rec2';
end;
This is a hack but it should work. You'll end up with a single record
var with your old fields and new fields, typed as you have specified.
On 3/1/07, Eddy D. Sanchez [EMAIL PROTECTED] wrote:
Good idea, thanks a lot, I try it
On Mar 2, 2007, at 1:32 AM, Postgres User
That's what I was afraid of... it's a new install of Win Server 2003 R2, so I can rule out any third party firewall. Windows Firewall isNOT installed. And I've installedPostgres on a Windows XP boxbehind the same router, so it's not a router-firewall issue.
It's probably a new R2
Hi,
Has anyone tried to install Postgres on Windows Sever 2003 version R2? R2 is actually shipping as a 'new' Microsoft product- it's basically an interim update to Windows Server (
http://www.microsoft.com/windowsserver2003/r2/whatsnewinr2.mspx).
I've installed Postgres on other versions of
82 matches
Mail list logo
