Re: [GENERAL] auto-filling a field on insert
Scott Marlowe wrote: > On Fri, Oct 23, 2009 at 9:32 PM, semi-ambivalent wrote: >> Thanks everyone for the pointers. I like the idea of getting rid of >> the concatenated field even though it reduced the worst case query >> time to one tenth of what I had been seeing. But for now I'm going to >> keep it there because I'm ignorant about triggers so this will be a >> good opportunity to learn about them before I drop the column for >> something more efficient, assuming there is. > > The multi column index should give you equivalent speed. In fact, it may well give you significantly superior speed, particularly if at least one of the columns always has a value specified for it in a query. Put that column first in the index column list and you'll only have to search for the other two values in the subset of the index that matches the first value. This could be a *lot* faster than a full index scan on your concatenated field. A trigger isn't necessary or desirable as a solution to this problem - you'd only need a trigger if you really want to have a "real" concatenated column. A multi-column index is almost certainly the best choice in this situation. A functional index can be used in situations where a multi-column index doesn't apply, or where you need something more complex, but here a multi-column index looks just ideal. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Conference 2009 Japan
2009/9/2 Markus Wanner : > Hi, > > I've added a wiki page with some information you might find helpful, if you > are attending the PostgreSQL Conference 2009 in Japan. However, I've never > been to Tokyo before, so please feel free to correct and add better links, > hints and recommendations: > > http://wiki.postgresql.org/wiki/PostgreSQL_Conference_2009_Japan > > I'd personally like to stay in a hotel with other fellow hackers, as those > late night discussions tend to be very inspiring as well. So, what hotel do > you plan to stay at? I'm actually living / working in Tokyo and though I'm not sure whether I'll be able to attend all of the conference I will certainly be around and it would be great to meet up. Also, if anyone needs any help / advice / translation etc., feel free to ask and I will do my best. Ian Barwick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can the string literal syntax for function definitions please be dropped ?
On Sat, Oct 24, 2009 at 5:41 PM, Tom Lane wrote: > Timothy Madden writes: > > Can the string literal syntax for the function body in a CREATE FUNCTION > > statement please, > > please be dropped ? > > No. Since the function's language might be anything, there's no way to > identify the end of the function body otherwise. > There is a SQL standard for this, and other DBMS look like they found a way ... How come it can not be done ?
Re: [GENERAL] Can the string literal syntax for function definitions please be dropped ?
On Sunday 25 October 2009 9:17:04 am Timothy Madden wrote: > On Sat, Oct 24, 2009 at 5:41 PM, Tom Lane wrote: > > Timothy Madden writes: > > > Can the string literal syntax for the function body in a CREATE > > > FUNCTION statement please, > > > please be dropped ? > > > > No. Since the function's language might be anything, there's no way to > > identify the end of the function body otherwise. > > There is a SQL standard for this, and other DBMS look like they found a way > ... > > How come it can not be done ? I am trying to determine the problem you are trying to solve. Even if the string literal syntax goes away functions created for Postgres make use of Postgres specific syntax and extensions. So there is going to be a translation step involved irregardless of the string issue. So just out of curiosty what problem does the string syntax cause? -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can the string literal syntax for function definitions please be dropped ?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > There is a SQL standard for this, and other DBMS look like they found a way > > How come it can not be done ? It *can* be done, but it won't be done. At least not by default. You might get better traction if you perhaps argue for a flag to pg_dump to exhibit the behavior you want. It has a small chance of being accepted, but a much greater chance than changing the default behavior. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200910251638 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkrkt2oACgkQvJuQZxSWSshyYwCcCRozshAfS22KtJJqoLmJdOsx X9IAmwYedkEyw819R6P0FZXVgskefaR4 =4oy5 -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can the string literal syntax for function definitions please be dropped ?
On Sun, Oct 25, 2009 at 8:49 PM, Adrian Klaver wrote: > On Sunday 25 October 2009 9:17:04 am Timothy Madden wrote: > > On Sat, Oct 24, 2009 at 5:41 PM, Tom Lane wrote: > > > Timothy Madden writes: > > > > Can the string literal syntax for the function body in a CREATE > > > > FUNCTION statement please, > > > > please be dropped ? > > > > > > No. Since the function's language might be anything, there's no way to > > > identify the end of the function body otherwise. > > > > There is a SQL standard for this, and other DBMS look like they found a > way > > ... > > > > How come it can not be done ? > > I am trying to determine the problem you are trying to solve. Even if the > string > literal syntax goes away functions created for Postgres make use of > Postgres > specific syntax and extensions. So there is going to be a translation step > involved irregardless of the string issue. So just out of curiosty what > problem > does the string syntax cause? > Just like when I write C++ applications I use standards-conforming C++, when I write SQL applications I would like to use standard-conforming SQL. I would normally write standard-conforming C++ code even when porting is not actually a stated requirement in my project, just because portable code is the right code. Should my project need some specific function or library, at least the platform-specific code should be grouped in a separate module/directory. I think there are many, many other developers that agree with me in this regard. After all PostgreSql is open-source and portable. For SQL, at the current conformance and compatibility level among DBMS providers in use today, one could rightly say there is no such thing as conforming or portable SQL application in real-world. However my intent is still the same, to write conforming (SQL) code. Or at least try, as much as it is possible. One day the world of DBMS providers will eventually get better in this regard. So I would expect any open source, state-of-the-art DBMS system to have standards-compliance as one of its goals (if not already one of its features). I am happy to say PosgreSQL rates pretty well, if not the best, when compared with other (commercial and non-commercial) DBMSs by standards-conformance. Maybe this is also why I have these high expectations ... Thank you, Timothy Madden
Re: [GENERAL] Can the string literal syntax for function definitions please be dropped ?
On Sun, Oct 25, 2009 at 10:38 PM, Greg Sabino Mullane wrote: > > -BEGIN PGP SIGNED MESSAGE- > Hash: RIPEMD160 > > > There is a SQL standard for this, and other DBMS look like they found a > way > > > > How come it can not be done ? > > It *can* be done, but it won't be done. At least not by default. You might > get better traction if you perhaps argue for a flag to pg_dump to > exhibit the behavior you want. It has a small chance of being accepted, > but a much greater chance than changing the default behavior. > > What I want is compatible with existing code and the current default behavior. Just look for a LANGUAGE SQL declaration in the function header (before the body). If found expect the in-place definition of the function body to follow. If not found expect a string literal that holds the function body to follow, with the LANGUAGE declaration after (default behavior). I am interested in the functions I write by hand as an application developer; pg_dump may dump the functions any way it finds suitable (although I would still prefer the conforming form). Thank you, Timothy Madden
Re: [GENERAL] Can the string literal syntax for function definitions please be dropped ?
On Sun, Oct 25, 2009 at 2:43 PM, Timothy Madden wrote: > > > On Sun, Oct 25, 2009 at 8:49 PM, Adrian Klaver wrote: >> >> On Sunday 25 October 2009 9:17:04 am Timothy Madden wrote: >> > On Sat, Oct 24, 2009 at 5:41 PM, Tom Lane wrote: >> > > Timothy Madden writes: >> > > > Can the string literal syntax for the function body in a CREATE >> > > > FUNCTION statement please, >> > > > please be dropped ? >> > > >> > > No. Since the function's language might be anything, there's no way >> > > to >> > > identify the end of the function body otherwise. >> > >> > There is a SQL standard for this, and other DBMS look like they found a >> > way >> > ... >> > >> > How come it can not be done ? >> >> I am trying to determine the problem you are trying to solve. Even if the >> string >> literal syntax goes away functions created for Postgres make use of >> Postgres >> specific syntax and extensions. So there is going to be a translation step >> involved irregardless of the string issue. So just out of curiosty what >> problem >> does the string syntax cause? > > Just like when I write C++ applications I use standards-conforming C++, when > I write SQL > applications I would like to use standard-conforming SQL. But as soon as the rubber hits the road, not two C or C++ compilers are really 100% compatible as are no two SQL implementations. Simply wanting things to be the same across all DBs seems kind of naive as a reason to change pg's behaviour. > I would normally write standard-conforming C++ code even when porting is not > actually a > stated requirement in my project, just because portable code is the right > code. So your argument is more philosophical than logical? Not that philosophy doesn't have its place, but a logical reason would carry far more weight here. > Should my > project need some specific function or library, at least the > platform-specific code should > be grouped in a separate module/directory. I think there are many, many > other developers > that agree with me in this regard. After all PostgreSql is open-source and > portable. I haven't seen them on this list really. I'm entirely against it, but if it breaks stuff I've already got that works and works well then I have no real need for it, especially if it's ONLY for the purpose of being SQL standard compliant and not for meeting some real world need. > For SQL, at the current conformance and compatibility level among DBMS > providers in use > today, one could rightly say there is no such thing as conforming or > portable SQL application > in real-world. A large part of the reason for this is that parts of the SQL spec are just plain strange and weird and implementing them gains us little or nothing. The SQL spec is far more open to interpretation than the C or C++ specs, and has changed a LOT more in the last ten years than those as well. It's a moving target in many ways, and while many parts of it make perfect sense to be implemented as written, a noticeable minority of it doesn't warrant implementation / changes to comply. > However my intent is still the same, to write conforming > (SQL) code. Or at least > try, as much as it is possible. One day the world of DBMS providers will > eventually get better > in this regard. They've been doing that very thing for the last 20 or so years. But I think that differences in implementation and philosophy will always result in some divergence of SQL interface. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can the string literal syntax for function definitions please be dropped ?
Timothy Madden escribió: > Just like when I write C++ applications I use standards-conforming > C++, when I write SQL applications I would like to use > standard-conforming SQL. Sadly, we don't have standards-conformant SQL/PSM. Right now, we have a lot of different languages for functions, none of them mandated by SQL, and there is no reason to create a syntax exception for any of them. I am sure that when we get SQL/PSM support, the interest in getting standards-conformant procedure creation statements is going to get a lot higher. PL/pgSQL is not SQL/PSM. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can the string literal syntax for function definitions please be dropped ?
Timothy Madden writes: > What I want is compatible with existing code and the current default > behavior. Just look for a LANGUAGE SQL declaration in the function > header (before the body). > If found expect the in-place definition of the function body to follow. > If not found expect a string literal that holds the function body to follow, > with the LANGUAGE declaration after (default behavior). This proposal is unfortunately complete nonsense, because it fails to address the question of how you figure out where the function body *ends*. We have to have a simple and not-language-specific rule for that. Even if the backend could be made smart enough to handle a variety of cases, we could hardly expect client-side code (like psql) to track all the cases. And psql does need to understand where the CREATE FUNCTION command ends, so that it can tell when to ship the command off to the backend. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can the string literal syntax for function definitions please be dropped ?
On Sun, Oct 25, 2009 at 11:33 PM, Scott Marlowe wrote: > On Sun, Oct 25, 2009 at 2:43 PM, Timothy Madden > wrote: > > Just like when I write C++ applications I use standards-conforming C++, > when > > I write SQL > > applications I would like to use standard-conforming SQL. > > But as soon as the rubber hits the road, not two C or C++ compilers > are really 100% compatible as are no two SQL implementations. > > > For SQL, at the current conformance and compatibility level among DBMS > > providers in use > > today, one could rightly say there is no such thing as conforming or > > portable SQL application > > in real-world. > > A large part of the reason for this is that parts of the SQL spec are > just plain strange and weird and implementing them gains us little or > nothing. The SQL spec is far more open to interpretation than the C > or C++ specs, and has changed a LOT more in the last ten years than > those as well. It's a moving target in many ways, and while many > parts of it make perfect sense to be implemented as written, a > noticeable minority of it doesn't warrant implementation / changes to > comply. > > I am only talking about conforming syntax for features PostgreSql already has. That could gain something, right ? And there are C/C++ applications that compile on many systems, like Postgres is, despite the fact that no two C++ compilers are 100% compatible. Thank you, Timothy Madden
Re: [GENERAL] Can the string literal syntax for function definitions please be dropped ?
On Sun, Oct 25, 2009 at 11:40 PM, Alvaro Herrera wrote: > Timothy Madden escribió: > > > Just like when I write C++ applications I use standards-conforming > > C++, when I write SQL applications I would like to use > > standard-conforming SQL. > > Sadly, we don't have standards-conformant SQL/PSM. Right now, we have a > lot of different languages for functions, none of them mandated by SQL, > and there is no reason to create a syntax exception for any of them. > > I am sure that when we get SQL/PSM support, the interest in getting > standards-conformant procedure creation statements is going to get a lot > higher. > > PL/pgSQL is not SQL/PSM. > > Anyway Posgres offers a CREATE FUNCTION statement that resembles or should resemble that in the standard, and that is what I am talking about. I just want the Postgres version of the statement to look more like the standard one. Would you detail the differences you talk about that you see here ? Thank you, Timothy Madden
Re: [GENERAL] Can the string literal syntax for function definitions please be dropped ?
On Mon, Oct 26, 2009 at 12:01 AM, Tom Lane wrote: > Timothy Madden writes: > > What I want is compatible with existing code and the current default > > behavior. Just look for a LANGUAGE SQL declaration in the function > > header (before the body). > > > If found expect the in-place definition of the function body to follow. > > If not found expect a string literal that holds the function body to > follow, > > with the LANGUAGE declaration after (default behavior). > > This proposal is unfortunately complete nonsense, because it fails to > address the question of how you figure out where the function body *ends*. > We have to have a simple and not-language-specific rule for that. Even > if the backend could be made smart enough to handle a variety of cases, > we could hardly expect client-side code (like psql) to track all the > cases. And psql does need to understand where the CREATE FUNCTION > command ends, so that it can tell when to ship the command off to the > backend. > By the standard the routine body is a and the question of how to figure out where the function body ends should be answered as such. I am talking about two cases, the one psql already handles, and the one where the body is (and ends as) a , which statement again psql should already understand and which is signaled by the LANGUAGE SQL declaration in the function header. Thank you, Timothy Madden
Re: [GENERAL] Can the string literal syntax for function definitions please be dropped ?
Timothy Madden escribió: > Anyway Posgres offers a CREATE FUNCTION statement that resembles or should > resemble that in the standard, and that is what I am talking about. "Should" being the operative word in that sentence. If you want to submit a patch to move us closer towards the SQL/PSM goal, I'm sure it will be welcome. > I just want the Postgres version of the statement to look more like > the standard one. Sure. If we weren't all pointing in that general direction, we would probably have CONNECT BY instead of WITH RECURSIVE. > Would you detail the differences you talk about that you see here ? I'm not that familiar with SQL/PSM, sorry. Pavel Stehule is da man. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can the string literal syntax for function definitions please be dropped ?
On Mon, Oct 26, 2009 at 12:42 AM, Alvaro Herrera wrote: > Timothy Madden escribió: > > > Anyway Posgres offers a CREATE FUNCTION statement that resembles or > should > > resemble that in the standard, and that is what I am talking about. > > "Should" being the operative word in that sentence. If you want to > submit a patch to move us closer towards the SQL/PSM goal, I'm sure it > will be welcome. > You know that takes quite some effort to invest. How are you sure a patch for this will be welcome when people here mostly disagree with me ? > > > I just want the Postgres version of the statement to look more like > > the standard one. > > Sure. If we weren't all pointing in that general direction, we would > probably have CONNECT BY instead of WITH RECURSIVE. > I don't understand, what CONNECT BY or WITH RECURSIVE ? Thank you, Timothy Madden
Re: [GENERAL] Can the string literal syntax for function definitions please be dropped ?
On Sunday 25 October 2009 3:20:51 pm Timothy Madden wrote: > On Mon, Oct 26, 2009 at 12:01 AM, Tom Lane wrote: > > Timothy Madden writes: > > > What I want is compatible with existing code and the current default > > > behavior. Just look for a LANGUAGE SQL declaration in the function > > > header (before the body). > > > > > > If found expect the in-place definition of the function body to follow. > > > If not found expect a string literal that holds the function body to > > > > follow, > > > > > with the LANGUAGE declaration after (default behavior). > > > > This proposal is unfortunately complete nonsense, because it fails to > > address the question of how you figure out where the function body > > *ends*. We have to have a simple and not-language-specific rule for that. > > Even if the backend could be made smart enough to handle a variety of > > cases, we could hardly expect client-side code (like psql) to track all > > the cases. And psql does need to understand where the CREATE FUNCTION > > command ends, so that it can tell when to ship the command off to the > > backend. > > By the standard the routine body is a and the > question of how to figure out where the function body ends should be > answered > as such. > > I am talking about two cases, the one psql already handles, and the one > where > the body is (and ends as) a , which statement > again psql should already understand and which is signaled by the > LANGUAGE SQL declaration in the function header. > > Thank you, > Timothy Madden You mean something like this ?: http://dev.mysql.com/doc/refman/5.1/en/stored-programs-defining.html I am not seeing that as an improvement. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can the string literal syntax for function definitions please be dropped ?
On Sunday 25 October 2009 4:06:33 pm Timothy Madden wrote: > On Mon, Oct 26, 2009 at 12:42 AM, Alvaro Herrera > > > wrote: > > > > Timothy Madden escribió: > > > Anyway Posgres offers a CREATE FUNCTION statement that resembles or > > > > should > > > > > resemble that in the standard, and that is what I am talking about. > > > > "Should" being the operative word in that sentence. If you want to > > submit a patch to move us closer towards the SQL/PSM goal, I'm sure it > > will be welcome. > > You know that takes quite some effort to invest. How are you sure a patch > for this will be > welcome when people here mostly disagree with me ? Since we are getting philosophical, I did not realize agreement was necessary to get things done:) I do agree with the effort assessment. To justify the effort though, it would seem you only need to convince yourself of the merits. Basically "A Field of Dreams" scenario. You build it and see who shows up. > > > > Thank you, > Timothy Madden -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can the string literal syntax for function definitions please be dropped ?
Timothy Madden escribió: > On Mon, Oct 26, 2009 at 12:42 AM, Alvaro Herrera > wrote: > > > Timothy Madden escribió: > > > > > Anyway Posgres offers a CREATE FUNCTION statement that resembles > > > or should resemble that in the standard, and that is what I am > > > talking about. > > > > "Should" being the operative word in that sentence. If you want to > > submit a patch to move us closer towards the SQL/PSM goal, I'm sure it > > will be welcome. > > You know that takes quite some effort to invest. How are you sure a patch > for this will be welcome when people here mostly disagree with me ? Because you're wielding the wrong argument :-) > > I just want the Postgres version of the statement to look more like > > the standard one. > > > > Sure. If we weren't all pointing in that general direction, we would > > probably have CONNECT BY instead of WITH RECURSIVE. > > I don't understand, what CONNECT BY or WITH RECURSIVE ? CONNECT BY is Oracle's way of implementing recursive queries. We had a patch for that for years, but it was rejected over and over on various grounds, one of which was that it was not the standard's spelling of the feature. We only got recursive queries when somebody was willing to bite the bullet and write it in WITH RECURSIVE form. My point here was: we definitely support the standard. We don't do the string literal bit for functions just because we like to be different. We do it because our extensibility features require it. Of course, SQL/PSM is a different beast than all the rest of the PLs, because it is standard, so I am sure that we will want to implement the standard syntax (no string literal) when we have SQL/PSM. But implementing no- string-literals before we get full SQL/PSM support would be pointless, because there are so many other things that are not standard in that area. Simply removing the quotes (which is what you are requesting) would not take our standards compliance much further. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can the string literal syntax for function definitions please be dropped ?
Alvaro Herrera writes:
> SQL/PSM is a different beast than all the rest of the PLs, because it is
> standard, so I am sure that we will want to implement the standard
> syntax (no string literal) when we have SQL/PSM. But implementing no-
> string-literals before we get full SQL/PSM support would be pointless,
> because there are so many other things that are not standard in that
> area. Simply removing the quotes (which is what you are requesting)
> would not take our standards compliance much further.
[ after re-reading the spec a little bit ... ]
One interesting point here is that I don't think the spec suggests
that SQL/PSM can be written in-line in the CREATE FUNCTION statement
at all. What I see (at least in SQL99) is
::=
CREATE
::=
{ | }
::=
FUNCTION
[ ]
::=
|
::=
and seems to allow one (count em, one) SQL DDL
or DML statement. So per spec, essentially every interesting case
requires an . We could possibly support the
single-SQL-statement case without any quotes --- at least, it doesn't
obviously break clients to do that; handling it inside the backend still
seems nontrivial. But it's not clear to me that that case is useful
enough to be worth the trouble.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can the string literal syntax for function definitions please be dropped ?
On Sun, 25 Oct 2009 20:17:22 -0400, Tom Lane wrote about Re: [GENERAL] Can the string literal syntax for function definitions please be dropped ?: [snip] > ::= > > | > > ::= > >and seems to allow one (count em, one) SQL >DDL or DML statement. So per spec, essentially every interesting case >requires an . This explains the evolution of DB2's support for user-defined functions: initially they (UDFs) had to be written in some host language (COBOL, PL/I, C, etc.), and linked in by external reference; later, a single SQL statement(*) was permitted instead; finally, a compound SQL statement was permitted, with BEGIN and END bracketing an arbitrary collection of other SQL statements. (*) Since all UDFs must return a value, the single statement was almost invariably a RETURN with some query providing the value. -- Regards, Dave [RLU #314465] === david.w.n...@ntlworld.com (David W Noon) === -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] indexing
I work on my thesis on spatial database indeksing using Btree n GiST. Can you help to explain how the GiST and btree indexing work in postgresql ( the algorithm ) ? Can somone show me how to write code to know the tree level and count the root and leaf ? thanks for the information n help . -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How can I get one OLD.* field in a dynamic query inside a trigger function ?
Le 24/10/09 22:57, Pavel Stehule a écrit :
2009/10/24 Bruno Baguette :
Which one would you advise me to learn and use, instead of PL/pgSQL ?
It depends on what you are know (if you known better perl or pthon).
Usually I using mainly plpgsql and on some functions plperl and C.
plpgsql is good language as glue of SQL statements, plperl is good for
IO and CPAN libraries, and C should help with some processor intensive
functions.
Hello Pavel !
I've read the doc' about plperl and plpython, since I have notion of both.
Because plpython is not a trusted language, I finally took plperl.
In case somebody else does look for the same thing as I was looking for,
here's the new trigger function, rewritten using plperl :
CREATE OR REPLACE FUNCTION delete_acl_trigger() RETURNS trigger AS
$delete_acl_trigger$
if($_TD->{argc}>0)
{
for(my $i=0; $i < $_TD->{argc} ; $i++)
{
my $acl_id = $_TD->{old}{$_TD->{"args"}[i]};
my $rv = spi_exec_query("SELECT delete_acl($acl_id);");
# DEBUG TOOLS
# my $status = $rv->{status};
# elog(NOTICE, 'RESULTAT : '.$status);
}
return;
}
else
{
return;
}
$delete_acl_trigger$ LANGUAGE plperl;
Do I have to mention that it does perfectly what I expected ? :-)
If you have any comments or tips about that function, feel free to do so
in order to improve it ! :-)
Pavel, many thanks for your tips and explanations !
They were very helpfull ! :-)
Kind regards,
--
Bruno Baguette - bruno.bague...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can the string literal syntax for function definitions please be dropped ?
2009/10/26 Tom Lane :
> Alvaro Herrera writes:
>> SQL/PSM is a different beast than all the rest of the PLs, because it is
>> standard, so I am sure that we will want to implement the standard
>> syntax (no string literal) when we have SQL/PSM. But implementing no-
>> string-literals before we get full SQL/PSM support would be pointless,
>> because there are so many other things that are not standard in that
>> area. Simply removing the quotes (which is what you are requesting)
>> would not take our standards compliance much further.
>
> [ after re-reading the spec a little bit ... ]
>
> One interesting point here is that I don't think the spec suggests
> that SQL/PSM can be written in-line in the CREATE FUNCTION statement
> at all. What I see (at least in SQL99) is
>
> ::=
> CREATE
>
> ::=
> { | }
>
>
>
> ::=
> FUNCTION
>
>
>
> [ ]
>
> ::=
>
> |
>
> ::=
>
> and seems to allow one (count em, one) SQL DDL
> or DML statement. So per spec, essentially every interesting case
> requires an . We could possibly support the
> single-SQL-statement case without any quotes --- at least, it doesn't
> obviously break clients to do that; handling it inside the backend still
> seems nontrivial. But it's not clear to me that that case is useful
> enough to be worth the trouble.
>
it is not correct. When you would to use more statements, then you can
to use BEGIN ... END;
so CREATE FUNCTION foo(...)
RETURNS int AS
BEGIN
DECLARE x int;
SET x = 10;
RETURN x;
END;
is correct.
CREATE FUNCTION foo(...)
RETURNS int AS
RETURN x;
is correct too. The block is optional in SQL/PSM.
http://www.postgres.cz/index.php/SQL/PSM_Manual
What I known, other DBMS have to solve this complications too. Next
possibility is using some special symbol for ending parser like
DELIMITER.
Actually we have a independent parsers for SQL and PL languages. It
has some adventages:
a) we could to support more PL languages
b) PL parser are relative small, SQL parser is relative clean
If we integrate some language to main parser, then we have to able to
block some parts of parser dynamicky - because some functionality
should be invisible from some PL - SQL/PSM FOR statement has different
syntax than PL/pgSQL FOR statement. I thing, so this is possible - but
it uglify parser. If somebody found way how to do extendable parser in
bison, then we could to go far, but actually I don't advantages change
anything on current syntax.
Regards
Pavel Stehule
> regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
