Thread: Using C# to create stored procedures
Any idea how to write server-side stored procedures in C# for PostgreSQL database ? In windows .NET 2 framework should be used and in Linuc/Mac/Windows MONO should be used for this. How to install MONO engine as server-side language to PostgreSQL ? How to call .NET dlls from PostgreSQL stored procedure ? Andrus.
On Fri, Mar 30, 2007 at 12:19:44PM +0300, Andrus wrote: > Any idea how to write server-side stored procedures in C# for PostgreSQL > database ? > > In windows .NET 2 framework should be used and in Linuc/Mac/Windows MONO > should be used for this. > > How to install MONO engine as server-side language to PostgreSQL ? > > How to call .NET dlls from PostgreSQL stored procedure ? This is not currently supported. PostgreSQL supports a lot of languages, but C#/.Net is not one of them. You could call them through a CLR launcher, but that's going to be a lot of work for you. //Magnus
On Fri, Mar 30, 2007 at 12:19:44PM +0300, Andrus wrote: > Any idea how to write server-side stored procedures in C# for > PostgreSQL database ? There's an old project called PL/Mono <http://gborg.postgresql.org/project/plmono/projdisplay.php>, but as far as I know it's unmaintained. You might want to try to contact the author. :) Cheers, David. > In windows .NET 2 framework should be used and in Linuc/Mac/Windows > MONO should be used for this. > > How to install MONO engine as server-side language to PostgreSQL ? > > How to call .NET dlls from PostgreSQL stored procedure ? > > Andrus. > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: 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 -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
Last change for this project was 3 years ago. So I think that it is dead. I'm writing application in C#. I expected that I can wrote stored procedures in C# also using something like mod_mono in Apache. So it seems that most reasonable way is to learn dreaded plpgsql language and write stored procedures in it. Andrus. >> Any idea how to write server-side stored procedures in C# for >> PostgreSQL database ? > > There's an old project called PL/Mono > <http://gborg.postgresql.org/project/plmono/projdisplay.php>, but as > far as I know it's unmaintained. You might want to try to contact the > author. :) > > Cheers, > David. > >> In windows .NET 2 framework should be used and in Linuc/Mac/Windows >> MONO should be used for this. >> >> How to install MONO engine as server-side language to PostgreSQL ? >> >> How to call .NET dlls from PostgreSQL stored procedure ? > >> >> Andrus. >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 1: 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 > > -- > David Fetter <david@fetter.org> http://fetter.org/ > phone: +1 415 235 3778 AIM: dfetter666 > Skype: davidfetter > > Remember to vote! > Consider donating to PostgreSQL: http://www.postgresql.org/about/donate > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
Andrus wrote: > Last change for this project was 3 years ago. > So I think that it is dead. > > I'm writing application in C#. > I expected that I can wrote stored procedures in C# also using something > like mod_mono in Apache. > > So it seems that most reasonable way is to learn dreaded plpgsql > language and write stored procedures in it. Who dreads PL/pgSQL? It's a pretty easy language to learn. Granted, not as easy as one you already know, but I'm not aware of any RDBMS that supports C# as a stored procedure language. -- Guy Rouillier
Guy Rouillier wrote: > Andrus wrote: >> Last change for this project was 3 years ago. >> So I think that it is dead. >> >> I'm writing application in C#. >> I expected that I can wrote stored procedures in C# also using >> something like mod_mono in Apache. >> >> So it seems that most reasonable way is to learn dreaded plpgsql >> language and write stored procedures in it. > > Who dreads PL/pgSQL? It's a pretty easy language to learn. It's fairly easy and fairly good as long as you're doing "db kind of things". There are other things that are harder to do - which is why we support a wide range of languages like perl, tcl, python, php, java etc. Speaking of which - you might want to look into PL/Java, given that Java syntax is fairly similar to C#. But if what you do is suitable for pl/pgsql, it's probably a better idea to use that one. > Granted, > not as easy as one you already know, but I'm not aware of any RDBMS that > supports C# as a stored procedure language. Both MS SQL Server and IBM DB2 (on windows) supports .net stored procedures in C#, VB, or any other .net hosted language. There may be others that do as well, but those are the two I know of. //Magnus
Magnus Hagander <magnus@hagander.net> writes:
> Both MS SQL Server and IBM DB2 (on windows) supports .net stored
> procedures in C#, VB, or any other .net hosted language.
Awhile back I read an article claiming that .NET could only host one
language, or at least only languages that differed merely in trivial
syntactic details --- its execution engine isn't flexible enough for
anything truly interesting. Haven't looked into that for myself
though ... any comments?
regards, tom lane
Tom,
Jim Hugunin (creator of Jython, which is Python on Java Virtual Machine) thought similiar:
"""wanted to understand how Microsoft could have screwed up so badly that the CLR was a worse platform for dynamic languages than the JVM. My plan was to take a couple of weeks to build a prototype implementation of Python on the CLR and then to use that work to write a short pithy article called, "Why the CLR is a terrible platform for dynamic languages""""
He tried it, wrote Ironpython, was hired by Microsoft...
http://blogs.msdn.com/hugunin/archive/2006/09/05/741605.aspx
So there is proof that .NET is usable for more then one language. (Not that I want to embrace that platform)
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
fx 01212-5-13695179
-
Python: the only language with more web frameworks than keywords.
Awhile back I read an article claiming that .NET could only host one
language, or at least only languages that differed merely in trivial
syntactic details --- its execution engine isn't flexible enough for
anything truly interesting.
Jim Hugunin (creator of Jython, which is Python on Java Virtual Machine) thought similiar:
"""wanted to understand how Microsoft could have screwed up so badly that the CLR was a worse platform for dynamic languages than the JVM. My plan was to take a couple of weeks to build a prototype implementation of Python on the CLR and then to use that work to write a short pithy article called, "Why the CLR is a terrible platform for dynamic languages""""
He tried it, wrote Ironpython, was hired by Microsoft...
http://blogs.msdn.com/hugunin/archive/2006/09/05/741605.aspx
So there is proof that .NET is usable for more then one language. (Not that I want to embrace that platform)
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
fx 01212-5-13695179
-
Python: the only language with more web frameworks than keywords.
On Tue, Apr 03, 2007 at 04:00:17AM -0400, Tom Lane wrote: > Magnus Hagander <magnus@hagander.net> writes: > > Both MS SQL Server and IBM DB2 (on windows) supports .net stored > > procedures in C#, VB, or any other .net hosted language. > > Awhile back I read an article claiming that .NET could only host one > language, or at least only languages that differed merely in trivial > syntactic details --- its execution engine isn't flexible enough for > anything truly interesting. Haven't looked into that for myself > though ... any comments? It can certainly host different languages - there are (to me known) implementations of C#, Visual Basic, JScript, Java, Python, Cobol and others. These langauges are certainly pretty different. The whole thing is designed with C# as the *primary* language, so there are definitly parts of that "leaked through" into requirements for other languages. But it's doable. That said, they'll always need *some* changes, and the framework is the framework regardless of which language (which makes code in cobol.net look really freakish. Then again, most cobol code look freakish to me). Haven't tried that one myself, but I can certainly tell that the VB.Net code is sufficiently VB:ish to make it very hard to read/use for someone who hates VB. But API calls are teh same, so it's at least *possible* to read it. //Magnus
Hello everyone !
I have this query :
annonces=> EXPLAIN ANALYZE SELECT * FROM annonces AS a WHERE
detect_time > CURRENT_TIMESTAMP - '7 DAY'::INTERVAL
AND detect_time >= '2006-10-30 16:17:45.064793'
AND vente
AND surface IS NOT NULL AND price IS NOT NULL
AND type_id IN
(1,12,24,17,18,19,20,33,35,50,51,7,52,4,13,41,14,16,26,28,43,53,15,29,30,31,45,32,34,46,47,6)
AND (a.zipcode IN (69001,69002,69003,69004,69005,69006,69007,69008,69009)
OR a.city_id IN (27595)
OR a.coords &&
'(45.74101689082,4.8371263505564),(45.75898310918,4.8628736494436)'::BOX) ;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on annonces a (cost=1657.06..7145.98 rows=1177
width=691) (actual time=118.342..118.854 rows=194 loops=1)
Recheck Cond: (((vente AND (zipcode = ANY
('{69001,69002,69003,69004,69005,69006,69007,69008,69009}'::integer[])))
OR (vente AND (city_id = 27595)) OR (coords &&
'(45.75898310918,4.8628736494436),(45.74101689082,4.8371263505564)'::box))
AND (detect_time > (now() - '7 days'::interval)) AND (detect_time >=
'2006-10-30 16:17:45.064793'::timestamp without time zone))
Filter: (vente AND (surface IS NOT NULL) AND (price IS NOT NULL) AND
(type_id = ANY
('{1,12,24,17,18,19,20,33,35,50,51,7,52,4,13,41,14,16,26,28,43,53,15,29,30,31,45,32,34,46,47,6}'::integer[])))
-> BitmapAnd (cost=1657.06..1657.06 rows=2465 width=0) (actual
time=118.294..118.294 rows=0 loops=1)
-> BitmapOr (cost=133.83..133.83 rows=4368 width=0) (actual
time=2.903..2.903 rows=0 loops=1)
-> Bitmap Index Scan on annonces_zip (cost=0.00..56.54
rows=1825 width=0) (actual time=0.599..0.599 rows=1580 loops=1)
Index Cond: ((vente = true) AND (zipcode = ANY
('{69001,69002,69003,69004,69005,69006,69007,69008,69009}'::integer[])))
-> Bitmap Index Scan on annonces_city (cost=0.00..43.30
rows=1904 width=0) (actual time=0.464..0.464 rows=1575 loops=1)
Index Cond: ((vente = true) AND (city_id = 27595))
-> Bitmap Index Scan on annonces_coords (cost=0.00..33.10
rows=640 width=0) (actual time=1.837..1.837 rows=2166 loops=1)
Index Cond: (coords &&
'(45.75898310918,4.8628736494436),(45.74101689082,4.8371263505564)'::box)
-> Bitmap Index Scan on annonces_date (cost=0.00..1522.68
rows=72241 width=0) (actual time=114.930..114.930 rows=68022 loops=1)
Index Cond: ((detect_time > (now() - '7 days'::interval))
AND (detect_time >= '2006-10-30 16:17:45.064793'::timestamp without time
zone))
Total runtime: 119.000 ms
(14 lignes)
The interesting part is :
Bitmap Index Scan on annonces_date (cost=0.00..1522.68 rows=72241
width=0) (actual time=114.930..114.930 rows=68022 loops=1)
It bitmapscans about half the table...
I realized this index was actually useless for all my queries, so I
dropped it, and behold :
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on annonces a (cost=133.83..7583.77 rows=1176
width=691) (actual time=5.483..18.731 rows=194 loops=1)
Recheck Cond: ((vente AND (zipcode = ANY
('{69001,69002,69003,69004,69005,69006,69007,69008,69009}'::integer[])))
OR (vente AND (city_id = 27595)) OR (coords &&
'(45.75898310918,4.8628736494436),(45.74101689082,4.8371263505564)'::box))
Filter: ((detect_time > (now() - '7 days'::interval)) AND (detect_time
>= '2006-10-30 16:17:45.064793'::timestamp without time zone) AND vente
AND (surface IS NOT NULL) AND (price IS NOT NULL) AND (type_id = ANY
('{1,12,24,17,18,19,20,33,35,50,51,7,52,4,13,41,14,16,26,28,43,53,15,29,30,31,45,32,34,46,47,6}'::integer[])))
-> BitmapOr (cost=133.83..133.83 rows=4368 width=0) (actual
time=2.648..2.648 rows=0 loops=1)
-> Bitmap Index Scan on annonces_zip (cost=0.00..56.54
rows=1825 width=0) (actual time=0.505..0.505 rows=1580 loops=1)
Index Cond: ((vente = true) AND (zipcode = ANY
('{69001,69002,69003,69004,69005,69006,69007,69008,69009}'::integer[])))
-> Bitmap Index Scan on annonces_city (cost=0.00..43.30
rows=1904 width=0) (actual time=0.415..0.415 rows=1575 loops=1)
Index Cond: ((vente = true) AND (city_id = 27595))
-> Bitmap Index Scan on annonces_coords (cost=0.00..33.10
rows=640 width=0) (actual time=1.725..1.725 rows=2166 loops=1)
Index Cond: (coords &&
'(45.75898310918,4.8628736494436),(45.74101689082,4.8371263505564)'::box)
Total runtime: 18.859 ms
I guess saving 68022 rows of index scan is worth it !
Now 18 ms to extract the rows I want from that table (not huge, but still
about 70 megabytes) is what I call : nice.
Just thought it might be useful to some of you. I should have remembered
KISS !
BTW, shouldn't the planner think about this also ? Bitmap-scanning half a
table is likely to be slower than seq scan anyway...
Listmail <lists@peufeu.com> writes:
> It bitmapscans about half the table...
Which PG version is this exactly? We've fooled with the
choose_bitmap_and heuristics quite a bit ...
regards, tom lane
On Tue, 03 Apr 2007 19:23:31 +0200, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Listmail <lists@peufeu.com> writes:
>> It bitmapscans about half the table...
>
> Which PG version is this exactly? We've fooled with the
> choose_bitmap_and heuristics quite a bit ...
>
> regards, tom lane
Version is 8.2.3.
David-- Mono is DotNet on SUSE Heres the main site ..beware this is rather complicated to install and configure but once I&c you can run .NET Framework as a SUSE Binary Image then allow the GAC to pull in assemblies This link will get you started http://www.mono-project.com/VMware_Image I dont believe Im saying this but Perl *might possibly be* an easier development environment for interfacing to Stored Procedures-- Then again if you have 3 or 4 servers in your LR and have time to install and configure this might be a worthwhile weekend project I know of a SUSE engineer that worked at Novell developing this so give me a shout if you REALLY get stuck Martin ______________________________________________ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. >From: David Fetter <david@fetter.org> >To: Andrus <kobruleht2@hot.ee> >CC: pgsql-general@postgresql.org >Subject: Re: [GENERAL] Using C# to create stored procedures >Date: Mon, 2 Apr 2007 10:43:24 -0700 >MIME-Version: 1.0 >Received: from postgresql.org ([200.46.204.71]) by >bay0-mc2-f20.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.2668); Mon, 2 >Apr 2007 10:45:23 -0700 >Received: from localhost (maia-4.hub.org [200.46.204.183])by postgresql.org >(Postfix) with ESMTP id 1CDD89FB698for <mgainty@hotmail.com>; Mon, 2 Apr >2007 14:45:23 -0300 (ADT) >Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org >[200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 74312-01 for ><mgainty@hotmail.com>; Mon, 2 Apr 2007 14:45:22 -0300 (ADT) >Received: from postgresql.org (postgresql.org [200.46.204.71])by >postgresql.org (Postfix) with ESMTP id 07BB29FB30Ffor ><mgainty@hotmail.com>; Mon, 2 Apr 2007 14:45:22 -0300 (ADT) >Received: from localhost (maia-4.hub.org [200.46.204.183])by postgresql.org >(Postfix) with ESMTP id 58CBE9FB2E7for ><pgsql-general-postgresql.org@postgresql.org>; Mon, 2 Apr 2007 14:43:30 >-0300 (ADT) >Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org >[200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 70979-10 for ><pgsql-general-postgresql.org@postgresql.org>; Mon, 2 Apr 2007 14:43:25 >-0300 (ADT) >Received: from fetter.org (start.fetter.org [66.92.188.65])by >postgresql.org (Postfix) with ESMTP id B7C6A9FB2E4for ><pgsql-general@postgresql.org>; Mon, 2 Apr 2007 14:43:25 -0300 (ADT) >Received: by fetter.org (Postfix, from userid 500)id 4468AF3CBC9; Mon, 2 >Apr 2007 10:43:24 -0700 (PDT) >X-Message-Info: >LsUYwwHHNt2AlwlyMK8asddYvQnrjJPbBh1DBkmlDwlkxe5CK3eB33QYQy5fK3wA >X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 >References: <euikod$1ssc$1@news.hub.org> >User-Agent: Mutt/1.4.2.2i >X-Virus-Scanned: Maia Mailguard 1.0.1 >X-Mailing-List: pgsql-general >List-Archive: <http://archives.postgresql.org/pgsql-general> >List-Help: <mailto:majordomo@postgresql.org?body=help> >List-ID: <pgsql-general.postgresql.org> >List-Owner: <mailto:pgsql-general-owner@postgresql.org> >List-Post: <mailto:pgsql-general@postgresql.org> >List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-general> >List-Unsubscribe: ><mailto:majordomo@postgresql.org?body=unsub%20pgsql-general> >Precedence: bulk >Return-Path: pgsql-general-owner+m111972=mgainty=hotmail.com@postgresql.org >X-OriginalArrivalTime: 02 Apr 2007 17:45:23.0783 (UTC) >FILETIME=[B0D27570:01C7754E] > >On Fri, Mar 30, 2007 at 12:19:44PM +0300, Andrus wrote: > > Any idea how to write server-side stored procedures in C# for > > PostgreSQL database ? > >There's an old project called PL/Mono ><http://gborg.postgresql.org/project/plmono/projdisplay.php>, but as >far as I know it's unmaintained. You might want to try to contact the >author. :) > >Cheers, >David. > > > In windows .NET 2 framework should be used and in Linuc/Mac/Windows > > MONO should be used for this. > > > > How to install MONO engine as server-side language to PostgreSQL ? > > > > How to call .NET dlls from PostgreSQL stored procedure ? > > > > > Andrus. > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: 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 > >-- >David Fetter <david@fetter.org> http://fetter.org/ >phone: +1 415 235 3778 AIM: dfetter666 > Skype: davidfetter > >Remember to vote! >Consider donating to PostgreSQL: http://www.postgresql.org/about/donate > >---------------------------(end of broadcast)--------------------------- >TIP 2: Don't 'kill -9' the postmaster _________________________________________________________________ Exercise your brain! Try Flexicon. http://games.msn.com/en/flexicon/default.htm?icid=flexicon_hmemailtaglineapril07
Listmail <lists@peufeu.com> writes:
> On Tue, 03 Apr 2007 19:23:31 +0200, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Listmail <lists@peufeu.com> writes:
>>> It bitmapscans about half the table...
>>
>> Which PG version is this exactly? We've fooled with the
>> choose_bitmap_and heuristics quite a bit ...
> Version is 8.2.3.
Hmmm [ studies query a bit more... ] I think the reason why that index
is so expensive to use is exposed here:
>>> Index Cond: ((detect_time > (now() - '7 days'::interval)) AND (detect_time >= '2006-10-30
16:17:45.064793'::timestampwithout time zone))
Evidently detect_time is timestamp without time zone, but you're
comparing it to an expression that is timestamp with time zone
(ie CURRENT_TIMESTAMP). That's an enormously expensive operator
compared to straight comparisons of two timestamps of the same ilk,
because it does some expensive stuff to convert across time zones.
And you're applying it to a whole lot of index rows.
If you change the query to use LOCALTIMESTAMP to avoid the type
conversion, how do the two plans compare?
regards, tom lane
> Hmmm [ studies query a bit more... ] I think the reason why that index
> is so expensive to use is exposed here:
>
>>>> Index Cond: ((detect_time > (now() - '7
>>>> days'::interval)) AND (detect_time >= '2006-10-30
>>>> 16:17:45.064793'::timestamp without time zone))
>
> Evidently detect_time is timestamp without time zone, but you're
> comparing it to an expression that is timestamp with time zone
> (ie CURRENT_TIMESTAMP). That's an enormously expensive operator
> compared to straight comparisons of two timestamps of the same ilk,
> because it does some expensive stuff to convert across time zones.
> And you're applying it to a whole lot of index rows.
>
> If you change the query to use LOCALTIMESTAMP to avoid the type
> conversion, how do the two plans compare?
>
> regards, tom lane
OK, I recreated the index, and... you were right. Actually, it was my
query that sucked.
>>>> Index Cond: ((detect_time > (now() - '7
>>>> days'::interval)) AND (detect_time >= '2006-10-30
>>>> 16:17:45.064793'::timestamp without time zone))
Is it greater() which returns the highest of two values ? (like max() but
not aggregate)
Anyway, I fixed this in the code that generates the query, it's cleaner.
So now, I just put a constant timestamp.
Then we have this interesting side effect. Simply changing the timestamp
value induces a different plan, and the one which returns more rows is
actually faster !
annonces=> EXPLAIN ANALYZE SELECT * FROM annonces AS a
WHERE
detect_time >= '2007-03-27 20:46:29.187131+02'
AND vente
AND surface IS NOT NULL AND price IS NOT NULL
AND type_id IN
(1,12,24,17,18,19,20,33,35,50,51,7,52,4,13,41,14,16,26,28,43,53,15,29,30,31,45,32,34,46,47,6)
AND (a.zipcode IN (69001,69002,69003,69004,69005,69006,69007,69008,69009)
OR a.city_id IN (27595)
OR a.coords &&
'(45.74101689082,4.8371263505564),(45.75898310918,4.8628736494436)'::BOX) ;
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on annonces a (cost=1422.91..6758.82 rows=1130
width=691) (actual time=27.007..27.542 rows=194 loops=1)
Recheck Cond: (((vente AND (zipcode = ANY
('{69001,69002,69003,69004,69005,69006,69007,69008,69009}'::integer[])))
OR (vente AND (city_id = 27595)) OR (coords &&
'(45.75898310918,4.8628736494436),(45.74101689082,4.8371263505564)'::box))
AND (detect_time >= '2007-03-27 20:46:29.187131'::timestamp without time
zone))
Filter: (vente AND (surface IS NOT NULL) AND (price IS NOT NULL) AND
(type_id = ANY
('{1,12,24,17,18,19,20,33,35,50,51,7,52,4,13,41,14,16,26,28,43,53,15,29,30,31,45,32,34,46,47,6}'::integer[])))
-> BitmapAnd (cost=1422.91..1422.91 rows=2367 width=0) (actual
time=26.960..26.960 rows=0 loops=1)
-> BitmapOr (cost=133.80..133.80 rows=4368 width=0) (actual
time=2.764..2.764 rows=0 loops=1)
-> Bitmap Index Scan on annonces_zip (cost=0.00..56.54
rows=1825 width=0) (actual time=0.503..0.503 rows=1580 loops=1)
Index Cond: ((vente = true) AND (zipcode = ANY
('{69001,69002,69003,69004,69005,69006,69007,69008,69009}'::integer[])))
-> Bitmap Index Scan on annonces_city (cost=0.00..43.30
rows=1904 width=0) (actual time=0.457..0.457 rows=1575 loops=1)
Index Cond: ((vente = true) AND (city_id = 27595))
-> Bitmap Index Scan on annonces_coords (cost=0.00..33.10
rows=640 width=0) (actual time=1.802..1.802 rows=2166 loops=1)
Index Cond: (coords &&
'(45.75898310918,4.8628736494436),(45.74101689082,4.8371263505564)'::box)
-> Bitmap Index Scan on annonces_timestamp (cost=0.00..1288.58
rows=69375 width=0) (actual time=23.906..23.906 rows=68022 loops=1)
Index Cond: (detect_time >= '2007-03-27
20:46:29.187131'::timestamp without time zone)
Total runtime: 27.669 ms
(14 lignes)
annonces=> EXPLAIN ANALYZE SELECT * FROM annonces AS a WHERE
detect_time >= '2006-03-27 20:46:29.187131+02'
AND vente
AND surface IS NOT NULL AND price IS NOT NULL
AND type_id IN
(1,12,24,17,18,19,20,33,35,50,51,7,52,4,13,41,14,16,26,28,43,53,15,29,30,31,45,32,34,46,47,6)
AND (a.zipcode IN (69001,69002,69003,69004,69005,69006,69007,69008,69009)
OR a.city_id IN (27595)
OR a.coords &&
'(45.74101689082,4.8371263505564),(45.75898310918,4.8628736494436)'::BOX) ;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on annonces a (cost=134.51..7551.69 rows=2086
width=691) (actual time=3.372..6.517 rows=1063 loops=1)
Recheck Cond: ((vente AND (zipcode = ANY
('{69001,69002,69003,69004,69005,69006,69007,69008,69009}'::integer[])))
OR (vente AND (city_id = 27595)) OR (coords &&
'(45.75898310918,4.8628736494436),(45.74101689082,4.8371263505564)'::box))
Filter: ((detect_time >= '2006-03-27 20:46:29.187131'::timestamp
without time zone) AND vente AND (surface IS NOT NULL) AND (price IS NOT
NULL) AND (type_id = ANY
('{1,12,24,17,18,19,20,33,35,50,51,7,52,4,13,41,14,16,26,28,43,53,15,29,30,31,45,32,34,46,47,6}'::integer[])))
-> BitmapOr (cost=134.51..134.51 rows=4368 width=0) (actual
time=2.643..2.643 rows=0 loops=1)
-> Bitmap Index Scan on annonces_zip (cost=0.00..56.54
rows=1825 width=0) (actual time=0.537..0.537 rows=1580 loops=1)
Index Cond: ((vente = true) AND (zipcode = ANY
('{69001,69002,69003,69004,69005,69006,69007,69008,69009}'::integer[])))
-> Bitmap Index Scan on annonces_city (cost=0.00..43.30
rows=1904 width=0) (actual time=0.385..0.385 rows=1575 loops=1)
Index Cond: ((vente = true) AND (city_id = 27595))
-> Bitmap Index Scan on annonces_coords (cost=0.00..33.10
rows=640 width=0) (actual time=1.719..1.719 rows=2166 loops=1)
Index Cond: (coords &&
'(45.75898310918,4.8628736494436),(45.74101689082,4.8371263505564)'::box)
Total runtime: 7.129 ms
> Who dreads PL/pgSQL? It's a pretty easy language to learn. Granted, not > as easy as one you already know, but I'm not aware of any RDBMS that > supports C# as a stored procedure language. I needd to re-write a lot of compliatated SQL select statements to run them in server which generate reports. Currently they are running in client side. Client application uses procedural language to do additional processing of data retrieved from PostgreSQL server. I need to create new client application. So I desided that I must move as much processing to server as possible. Using C# should make my procedures portable to MS SQL, DB2 also. I'm looking for things available in modern language IDEs like auto-completion, intellisence, immediate syntax check and modern language features like classes, generic, interfaces. There a much more source code, resources and books available in C# than in pl/pgSQL. C# allows to use whole .NET class library, which contains solutions for almost everything. I think that using pgAdmin+PL/pgSQL decreases my productivity a lot compared to Visual Studio + C# Andrus.
2007/4/3, Andrus <kobruleht2@hot.ee>: > I needd to re-write a lot of compliatated SQL select statements to run them > in server which generate reports. > Currently they are running in client side. > Client application uses procedural language to do additional processing of > data retrieved from PostgreSQL server. > > I need to create new client application. So I desided that I must move as > much processing to server as possible. > Using C# should make my procedures portable to MS SQL, DB2 also. > > I'm looking for things available in modern language IDEs like > auto-completion, intellisence, immediate syntax check > and modern language features like classes, generic, interfaces. > There a much more source code, resources and books available in C# than in > pl/pgSQL. So use pljava. And add refactoring and IDE independence on the development side and platform independence on the deployment side, while you're at it. Or write a service layer and build upon it in whatever you want. Or write plc# and make it possible for yourself and anyone else to write postgresql procedures in C#. > C# allows to use whole .NET class library, which contains solutions for > almost everything. ...rrright. ;^) > I think that using pgAdmin+PL/pgSQL decreases my productivity a lot compared > to Visual Studio + C# I would tend to agree with you there: writing major chunks of logic in stored procedures (plpgsql, tsql or any other SQLoid language) isn't a very pleasurable experiance...but it feels like a death row pardon compared to debugging someone elses SQLoid code. Cheers, t.n.a.
> Awhile back I read an article claiming that .NET could only host one > language, or at least only languages that differed merely in trivial > syntactic details --- its execution engine isn't flexible enough for > anything truly interesting. Haven't looked into that for myself > though ... any comments? Well, I've heard it only really supports single-dispatch style of OO, so Common Lisp/Dylan type object models are not well supported. -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice
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 you're better off with SQL Server 2005. By the way, .NET LINQ will make most of your current thinking irrelevant in about 12 months. On 4/2/07, Andrus <kobruleht2@hot.ee> wrote: > Last change for this project was 3 years ago. > So I think that it is dead. > > I'm writing application in C#. > I expected that I can wrote stored procedures in C# also using something > like mod_mono in Apache. > > So it seems that most reasonable way is to learn dreaded plpgsql language > and write > stored procedures in it. > > Andrus. > > > >> Any idea how to write server-side stored procedures in C# for > >> PostgreSQL database ? > > > > There's an old project called PL/Mono > > <http://gborg.postgresql.org/project/plmono/projdisplay.php>, but as > > far as I know it's unmaintained. You might want to try to contact the > > author. :) > > > > Cheers, > > David. > > > >> In windows .NET 2 framework should be used and in Linuc/Mac/Windows > >> MONO should be used for this. > >> > >> How to install MONO engine as server-side language to PostgreSQL ? > >> > >> How to call .NET dlls from PostgreSQL stored procedure ? > > > >> > >> Andrus. > >> > >> > >> ---------------------------(end of broadcast)--------------------------- > >> TIP 1: 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 > > > > -- > > David Fetter <david@fetter.org> http://fetter.org/ > > phone: +1 415 235 3778 AIM: dfetter666 > > Skype: davidfetter > > > > Remember to vote! > > Consider donating to PostgreSQL: http://www.postgresql.org/about/donate > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: Don't 'kill -9' the postmaster > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ >