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 CCFL
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? I
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
an
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 in
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
mig
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 <
diegoaugustomol...@
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, 201
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 publ
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 wrote:
> On Sat, Dec 19, 2009 at 1:30 PM, Postgres User
> wrote:
>>
>> T
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?
On Sat, Dec 19, 2009 at 6:56 AM, Merlin Moncure wrote:
> On Sat, Dec 19, 2009 at 1:05 AM, Postgres User
> wrote:
>>
>> BEGIN
>> SELECT array_agg(category_id) INTO cat_list FROM (
>> WITH RECURSIVE subcategory AS
>> (
>
On Fri, Dec 18, 2009 at 9:53 PM, Merlin Moncure wrote:
> On Fri, Dec 18, 2009 at 11:35 PM, Pavel Stehule
> wrote:
>> 2009/12/19 Postgres User :
>>> Hi,
>>>
>>> I'm trying to write a very simple function statement to select a
>>> single int
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 A
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 ta
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 bu
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 bu
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 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.
> there is
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
>> >
>> > 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
>> 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 bui
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 c
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 tabl
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 Lane wrote:
> Postgres User writes:
>> In the docs, I see repeated references to $user in the postgresql.conf
>> schema search_path setting such as:
>
&
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 r
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 from
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 t
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 th
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 wrote:
> Good point, I should have specified 8.3.7.
>
> Just one more reason to anxiously anticipate upgrading to 8.4.
>
>
>
> On 30/05/2009,
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 f
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 sequen
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:
http:
bert depesz lubaczewski
<[EMAIL PROTECTED]> wrote:
> On Sun, May 11, 2008 at 11:28: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
> > additi
an Oosterhout
<[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 SQ
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.
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(
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 the
tion just 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 val
ition 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 P
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 expr
: select * 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
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
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 wha
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
Feb 19, 2008 8:45 PM, 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 = &
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
$body$
LANGUAGE 'plpgsql';
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.
&g
---
0
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
ECTED]> wrote:
> Martijn 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
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 da
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
t;
>
>
> -Original Message-
> From: [EMAIL PROTECTED] on behalf of Gregory Williamson
> Sent: Thu 11/29/2007 10:37 PM
> To: Postgres User; pgsql-general
> Subject: Re: [GENERAL] Simple math statement - problem
>
> The question:
> >
> &
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 / 10
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.
I'm new to Regex in Postgres. Can someone give me a quick pointer on
how I'd SELECT the substring between ''and '' in
a field?
Sample field data:
address city here Rogers, Jim zip code place
and I'd like the SELECT to return only:
Rogers, Jim
Thanks!
---(end of
> > 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
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:
&g
Hi,
I'm new to Regex in Postgres. Can someone give me a quick pointer on
how I'd SELECT the substring between ''and '' in
a field?
Sample field data:
address city here Rogers, Jim zip code place
and I'd like the SELECT to return only:
Rogers, Jim
Thanks!
---(en
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 tab
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 tab
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 table
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()
beg
/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
> transact
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:
FuelI
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 bro
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 n
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
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
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 no
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
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 about
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 table,
wrote:
Postgres User wrote:
> 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
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', 1)
select mt.fieldlist ' || new_fields || ' from 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
In c_entry';
Any other thoughts?
On 3/1/07, Tom Lane <[EMAIL PROTECTED]> wrote:
"Postgres User" <[EMAIL PROTECTED]> writes:
> So I added the following code:
> ref_entry = 'c_entry';
> MOVE Backward All In c_entry;
You have to use EXECUTE f
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- sele
lect_statement
LOOP
FETCH ref_entry INTO rec;
EXIT WHEN NOT FOUND;
i = i + 1;
IF v_list != '' THEN
v_list = v_list || ', ';
END IF;
v_list = v_list || rec.entry_id::varchar;
END LOOP;
MOVE Backward All In c_entry;
EN
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
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 is NOT installed. And I've installed Postgres on a Windows XP box behind the same router, so it's not a router-firewall issue.
It's probably a new R2 feature, I
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 W
82 matches
Mail list logo
