Hi,
I'm cross-posting this to the plpgsql list:
We've run into a small problem creating a set-returning function for
PostGIS in PostgreSQL 8.1.0:
CREATE OR REPLACE FUNCTION generate_x (geom geometry)
RETURNS SETOF double precision AS
'DECLARE
index integer;
BEGIN
FOR index IN 1 .. npoint
Hi, Regina,
Obe, Regina DND\MIS wrote:
> generate_series works I think because it is a special case - it isn't
> really a set function even though it behaves that way sortof.
Well, dump() works as well, and it is no build-in.
As a recent thread here stated, it is how set returning functions are
Hi, Steve,
[EMAIL PROTECTED] wrote:
> SELECT * from some_table WHERE
> test_for_equality_is_syntactically_ugly;
> The WHERE clause expects the function to return a boolean value. I
> can certainly return a boolean value from a function, but here it
> seems to me that what the function really ha
On Mar 27, 2006, at 5:41 AM, Markus Schaber wrote:
navteq=# select foo,generate_x(bar) from test;
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "generate_x" line 5 at return next
However, it is fine to call other set returning functions in t
On Sat, Mar 25, 2006 at 12:17:08AM +0100, PFC wrote:
>
>
> CREATE OR REPLACE FUNCTION foreach( liste INTEGER[] ) RETURNS SETOF
> INTEGER AS $$
> DECLARE
> i INTEGER;
> BEGIN
> FOR i IN 1..icount(liste) LOOP
> RETURN NEXT liste[i];
> END LOOP;
> END;
> $$ LANGUAGE plpgsql;
Seems like
On Fri, Mar 24, 2006 at 02:29:10PM -0800, Stephan Szabo wrote:
>
> On Fri, 24 Mar 2006, Julie Robinson wrote:
>
> > This works, but is there a better solution?
> >
> > select *
> > from quality_control_reset T
> > where date = (
> > select max(date)
> > from quality_control_reset
> >
On Fri, Mar 24, 2006 at 06:29:25PM +0100, PFC wrote:
>
>
> >>And I want to link the band to the album, but, if the album is a
> >>compilation it'll be linked to multiple band.ids, so i can't just add
> >>a column like:
>
> For a compilation, you should link a band to a track, not an album.
On Fri, Mar 24, 2006 at 04:00:35PM -0500, Tom Lane wrote:
> Amos Hayes <[EMAIL PROTECTED]> writes:
> > I'm trying to build a query that among other things, returns the
> > minimum and maximum values contained in either of two columns.
>
> I think you might be looking for
>
> select greate
Hi, John,
John DeSoi wrote:
> With SRFs, you need to specify what you want to select. In other words
> if you are calling generate_x(bar) you need "select * from
> generate_x(bar)" -- "select generate_x(bar)" will not work.
So, then, why does it work with generate_series() and dump()?
Confuse
Hi, Weimao Ke,
Weimao Ke wrote:
>> SELECT aid, cat
>> FROM tablename AS t
>> JOIN (SELECT aid, max(weight) AS weight
>> FROM tablename
>> GROUP BY aid) AS s USING (aid, weight);
>>
> This query will return duplicates if there are multiple categories (for
> one aid) with the same max weig
Hi, Eugene,
Eugene E. wrote:
> This means that some privileges are NOT INDEPENDENT.
No, it means that the UPDATE operation needs both UPDATE and SELECT
privileges.
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against sof
Hi, Regina,
Obe, Regina DND\MIS wrote:
> Actually I suspect no set returning function created in pgsql will work
> the way you are trying to do it. Not sure why. The dump is done in c
> and plugged in and I think it explicitly generates a tuple in the target
> set for every output.
>
> Try rew
Hi Markus,
On Mar 27, 2006, at 9:00 AM, Markus Schaber wrote:
So, then, why does it work with generate_series() and dump()?
I'm not sure. All I know is I spent a while the other day puzzling
over the same error message you had and finally realized I had to add
a select expression to fix i
Hi,
In a plpgsl function, consider the following excerpt:
DECLARE
provider_id INTEGER;
BEGIN
provider_id := (SELECT provider_id FROM investment_products WHERE id =
my_new.investment_product_id);
END;
After a lot of trouble, I found out this line doesn't work correctly
with the variable name
On Mon, Mar 27, 2006 at 04:33:55PM +0200, Wiebe Cazemier wrote:
> Hi,
>
> In a plpgsl function, consider the following excerpt:
>
> DECLARE
> provider_id INTEGER;
> BEGIN
> provider_id := (SELECT provider_id FROM investment_products WHERE id =
> my_new.investment_product_id);
> END;
>
> Afte
Hi, Fay,
First, this would be better posted to the PostGIS lists, as pgsql2shp is
a PostGIS utility, and not a PostgreSQL SQL query.
Fay Du wrote:
> The command I used is: pgsql2shp -f newroads gisdb testarea
>
> Where, newroads is my out put file name, gisdb is database name,
> and te
Wiebe Cazemier <[EMAIL PROTECTED]> writes:
> DECLARE
> provider_id INTEGER;
> BEGIN
> provider_id := (SELECT provider_id FROM investment_products WHERE id =
> my_new.investment_product_id);
> END;
> After a lot of trouble, I found out this line doesn't work correctly
> with the variable name a
On Mon, Mar 27, 2006 at 10:02:24AM -0500, Tom Lane wrote:
> Wiebe Cazemier <[EMAIL PROTECTED]> writes:
> > DECLARE
> > provider_id INTEGER;
> > BEGIN
> > provider_id := (SELECT provider_id FROM investment_products WHERE id =
> > my_new.investment_product_id);
> > END;
>
> > After a lot of trou
On Mon, 27 Mar 2006, Markus Schaber wrote:
> Hi, John,
>
> John DeSoi wrote:
>
> > With SRFs, you need to specify what you want to select. In other words
> > if you are calling generate_x(bar) you need "select * from
> > generate_x(bar)" -- "select generate_x(bar)" will not work.
>
> So, then, wh
SELECT array_accum( DISTINCT list_id ) FROM bookmarks;
array_accum
---
{1,2,3,4,5,7}
Couldn't you just use array()?
Yes, you can do this :
SELECT ARRAY( SELECT something with one column );
However, array_accum() as an aggregate is more interesting because you
can
Hi, Stephan,
Stephan Szabo wrote:
> It's an implementation detail. Some languages handle SRFs in a way that
> can be handled in the select list (SQL and C I think) and others do not
> (plpgsql).
Ah, that's an enlightening explanation, thanks.
> The latter will likely change at some point, altho
Markus Schaber <[EMAIL PROTECTED]> writes:
> navteq=# select foo,generate_x(bar) from test;
> ERROR: set-valued function called in context that cannot accept a set
> CONTEXT: PL/pgSQL function "generate_x" line 5 at return next
plpgsql SRFs don't support being called from the SELECT target list,
Dear List;
If I have two threads modifying the same "bit" field:
thread1=> update table set bf=bf | '01000'
thread2=> update table set bf=bf | '1'
Will this operation always be safe (e.g. result in bf='11000')? Or must
I wrap things in
explicit transactions?
My application is to give
I'm using the current Windows psqlODBC driver. "Show system" tables is
turned off, but not working. When I use an ODBC client I see all
tables... including system tables, and views & tables for which no
permission exists.
Anyone else see this? And where can I submit a bug report (I've looked,
b
Bryce Nesbitt wrote:
> If I have two threads modifying the same "bit" field:
> thread1=> update table set bf=bf | '01000'
> thread2=> update table set bf=bf | '1'
> Will this operation always be safe (e.g. result in bf='11000')? Or
> must I wrap things in
> explicit transactions?
Each
Am Sonntag, 26. März 2006 23:47 schrieb Bryce Nesbitt:
> Dear List;
>
> If I have two threads modifying the same "bit" field:
> thread1=> update table set bf=bf | '01000'
> thread2=> update table set bf=bf | '1'
> Will this operation always be safe (e.g. result in bf='11000')?
yes, T
(Whoops, pressed wrong reply button. Here it is correctly this time.)
On 03/27/06 16:48, Jim C. Nasby wrote:
>
>Sadly, overloading variable names between plpgsql and SQL is *highly*
>problematic. Because of this I *always* prefix plpgsql variables with
>something, such as p_ for parameters and v_
(Whoops, pressed wrong reply button. Here it is correctly this time.)
On 03/27/06 17:02, Tom Lane wrote:
>It'll retrieve whatever the current value of the plpgsql variable
>provider_id is. plpgsql always assumes that ambiguous names refer
>to its variables (indeed, it isn't even directly aware t
Hello ,
I try to translate my old functions from oracle but don't understand what is
wrong.
create or replace function uporzadkuj_reguly(text,text) RETURNS integer AS
'
DECLARE
tabela ALIAS FOR $1;
lancuch ALIAS FOR $2;
ret integer:=0;
licznik integer:=1;
rekord firewall%ROWTYPE;
reguly C
On 03/28/06 01:35, Maciej Piekielniak wrote:
>Hello ,
>
>I try to translate my old functions from oracle but don't understand what is
>wrong.
>
>create or replace function uporzadkuj_reguly(text,text) RETURNS integer AS
>'
>DECLARE
> tabela ALIAS FOR $1;
> lancuch ALIAS FOR $2;
> ret integer:=0;
Markus Schaber wrote:
Hi, Eugene,
Eugene E. wrote:
This means that some privileges are NOT INDEPENDENT.
No, it means that the UPDATE operation needs both UPDATE and SELECT
privileges.
Markus
thanx.
I already clear this to me.
---(end of broadcast)
hi again
i made one mod to dbmirror.
1) I implemented a mechanism to exclude attributes (columns) from
dbmirror'ed tables.
I added one table
CREATE TABLE dbmirror_exclude_attributes (
tblname character varying(100) NOT NULL,
attnames text[] NOT NULL
);
ALTER TABLE ONLY dbmirror_exclu
32 matches
Mail list logo
