Re: [SQL] function that returns a set of records and integer(both of them)
Hello 8.4 can inline SRF SQL immutable functions. so SELECT * FROM fce(param) should be efective like SELECT * from wrapped_tab where col = param Regards Pavel Stehule 2008/7/13 daniel blanco <[EMAIL PROTECTED]>: > Ok, i see... well. when i have to return a set of records i'll > use sql and when i have to do stored procedure of functions i'll use plpsql > in this case but you're telling me that it will change in the 8.4 version. > won't it? > > > > >> Date: Sun, 13 Jul 2008 07:06:07 +0200 >> From: [EMAIL PROTECTED] >> To: [EMAIL PROTECTED] >> Subject: Re: [SQL] function that returns a set of records and integer(both >> of them) >> CC: [email protected] >> >> Hello >> >> 2008/7/13 daniel blanco <[EMAIL PROTECTED]>: >> > Ok, thanks pavel. i think i'll try set of cursors. by >> > the >> > way in t-sql i did it as follows: >> > table users with a field called name >> > >> > create procedure list_user_by_name >> > @info_name varchar >> > as >> > declare @sw >> > begin >> > select @n=(select count(*) from users where name like >> > (@info_name+'%')) >> > if @sw>0 >> > begin >> > select * where name like (@info_name+'%') >> > return 1 >> > end >> > else >> > begin >> > return 0 >> > end >> > end >> > >> >> do you thing return_status or global variables? It doesn't exist in >> PostreSQL. Your sample is typical example, what can be in stored >> procedure for MS and what would not be on Oracle like rdbms. This code >> is little bit ineffective. You have to call seq scan on users two >> times. Minimally this code on pg and oracle is: >> >> create function list_user_by_name(info_name) >> returns users as $$ >> select * from users where name like $1 || '%' >> $$ language sql strict; >> -- you don't need logical info about returned set - this information >> is inside returned set, it is just empty or not empty. >> >> but it's better use directly well select than stored procedure in this >> case (8.4 will be different) - On Oracle like rdbsm SELECTs are not >> wrapped to procedures. >> >> p.s. postgresql doesn't support global (session variables) - this >> topic was discussed in different thread this week >> >> Regards >> Pavel >> >> > as you see i do a select and a return at the same time when de sw >> > variable >> > is > than 0. i was expecting to do a similiar function with plpgsql o >> > sql in >> > postgresql >> > >> > >> >> Date: Sat, 12 Jul 2008 09:05:45 +0200 >> >> From: [EMAIL PROTECTED] >> >> To: [EMAIL PROTECTED] >> >> Subject: Re: [SQL] function that returns a set of records and >> >> integer(both >> >> of them) >> >> CC: [email protected] >> >> >> >> 2008/7/11 daniel blanco <[EMAIL PROTECTED]>: >> >> > Hi Everyone >> >> > >> >> > I would like to know if i can create a function that returns a set of >> >> > record >> >> > with the sql statement: select and a integer, i mean both of them, >> >> > because i >> >> > remenber that in sql server (transact sql) i can do that in a stored >> >> > procedure doing a select statement and a return of a integer in the >> >> > same >> >> > procedure, >> >> >> >> when I worked with T-SQL this wasn't possible - stored procedure >> >> should return recordset or multirecord set or OUT variables. And it >> >> was solution. When I would returns some similar like you, I had to >> >> re turn two recordsets. >> >> >> >> i'm migrating to postgresql and i have stored procedures like >> >> > this in my sql server database, can i do that with postgresql? >> >> >> >> You cannot return integer and recordset from function now. There is >> >> one workaround, you can return set of cursors. >> >> >> >> Regards >> >> Pavel Stehule >> >> >> >> > >> >> > Thanks. >> >> > >> >> > Get news, entertainment and everything you care about at Live.com. >> >> > Check >> >> > it >> >> > out! >> >> >> >> -- >> >> Sent via pgsql-sql mailing list ([email protected]) >> >> To make changes to your subscription: >> >> http://www.postgresql.org/mailpref/pgsql-sql >> > >> > >> > >> > Connect to the next generation of MSN Messenger Get it now! > > > > Invite your mail contacts to join your friends list with Windows Live > Spaces. It's easy! Try it! -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] PERSISTANT PREPARE (another point of view)
>[snip] > What's wrong with using complex views, stored procedures, functions and > maybe even custom data types to accomplish what you want here? Hi Steve, Correct me if I’m wrong, but views can not accept parameters, and stored procedures require defining sets of custom data types or some other complicated ways in order to simply accept some parameters and return some recordsets useful to the calling application. In 90% of cases we simply call same SQL statements with slightly different parameters and then pass it to business logic of the program. SP's are fine, they allow complex looping, nesting, variable declaration etc. but 90% of time we just need a SQL with few parameters and a resulting recordsets. Just try to return SELECT * FROM WHERE LIKE using SP! You need to define a SET OF and then populate the result into this set consuming both time and memory (it states so in Postgre documentation). You can't write it as a view because it has a as a parameter. What’s the best solution having in mind that this query will be called hundreds of times by all clients connected to DB? Moreover, I've run absolutely same SQL statement as a PREPARED statement and as a Stored procedure returning SET OF CUSTOM TYPE. It contained several union queries over a database of 6 millions of records returning a recordset of 1.5 millions of records. It took 5 min. for the prepared statement to return the results vs. 16 minutes that was required by SP. Memory consumption in case of prepared statement was around 300 MB while it took over 800MB for SP. It could be that there is a more efficient way to return recordsets from SP's that I do not know, but I couldn't find it in Postgre documentation. Besides, table definitions tend to change during time. How often did you add columns to a table? In this case custom data types must be upgraded, as well as EVERY stored procedure using that type. In case of prepared statements, you can upgrade only those you need to. This is extremely useful for building and improving reports. [*** not important, just a presentation of something we found useful in other engines ***] There is an interesting concept in JET engine (with all of deficiency of the engine itself); it allows building PREPARED statements over other PREPARED statements automatically passing parameters by name to the underlying PREPARED statement. This concept allows for realization of a base SQL statement preparing data for profit&loss report, and then using it in another SQL statement by adding joins to some specific tables (sectors and employees for instance). That way within minutes you can produce a profit&loss by sectors, or by employees or whatever. What is the magic part, if we introduce new type of documents to our program that do influence profit&loss then we simply change the BASE prepared statement. And ALL reports based on it get "upgraded" automatically. Best regards, Milan Oparnica -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] PERSISTANT PREPARE (another point of view)
Tom Lane wrote: Most people around this project think that the best way to do that is to push as much logic as you can into server-side stored procedures. That gives you every advantage that a persistent-prepared-statement feature would offer, and more besides: It's simply to complicated to return recordsets through server-side stored procedures. They are obviously designed to do complex data manipulation, returning few output variables informing the caller about final results. Returning records through sets of user-defined-types is memory and performance waste (please see my previous post as reply to Steve for more details). Plus it's hard to maintain and make improvements to such a system. I hate to see 800 user types made for every query we made as stored procedure. I don't say it couldn't be done through sp but maybe you guys could provide us with much easier (and efficient) way to organize and fetch common SQL statements. Something between VIEWS and STORED PROCEDURES, something like PARAMETERIZED VIEWS or PERSISTENT PREPARED statement. Either way, it would be a place where we could use only PURE SQL syntax. I think it's 90% of what any database application does. > * you can push procedural logic, as well as SQL, out of the application Application developing tools usually offer bigger set of functions, objects, methods etc. than any DB stored procedure language can. There is also debugging, code version control software, team development software and lots of other stuff. It's just more efficient to keep the logic in the application part of the system. Just compare the IDE editors to any DB Admin Tool. > > * you can improve performance by reducing the number of network round > trips needed to accomplish a multi-SQL-statement task > >regards, tom lane > I couldn't agree more. Such tasks are decidedly for SP's. I'm thinking about 90% of simple to mid-simple tasks (reports, logins, retriving customer and item properties, warehouse inventory and other stuff) that are simple matter of SQL or SQL's in a union with few parameters for filtering the data. I see programmers hard-coding such SQL statements in PHP, C++, Delphi or VB projects. Why? Is it to complex to have it implemented in Postgre engine? We have PREPARE statement, locally for the user. Is it possible to take it globally, for all users, and not to forget it when all connections dye? It is a way to get all of SQL statement out of the application not only "as much logic as you can". As a leader of our development team I find it HIGHLY (and I mean HIGHLY) DESIRABLE. Best regards, Milan Oparnica -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] how to perform minus (-) operation in a dynamic query
Hai all, I am new to plpgsql ,I have a table structure: Column | Type | Modifiers +--+--- mf | double precision | sf | double precision | comm | integer | I create a the following funtion create or replace function test_perc() returns setof record as $body$ declare vchr_query VARCHAR(100); r record; begin vchr_query:= 'SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as flt_claim'; FOR r in EXECUTE vchr_query LOOP RETURN NEXT r; END LOOP; RETURN; end$body$ language 'plpgsql' function created but when I am traing to run this function I got the following error ERROR: syntax error at or near "–" at character 18 QUERY: SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as flt_claim CONTEXT: PL/pgSQL function "test_perc" line 7 at for over execute statement LINE 1: SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as... How I can solve this ,pls help me thanks in advance: Anoop
Re: [SQL] how to perform minus (-) operation in a dynamic query
am Mon, dem 14.07.2008, um 11:21:17 +0530 mailte Anoop G folgendes: > SELECT mf,sf,(mf mf * comm /100) (sf sf * comm/100) as flt_claim; ^^^ ^^^ ^^^ That's not valid SQL. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
