On Tue, May 28, 2013 at 1:56 PM, Brian Wolf <[email protected]>wrote:
> Excellent information.
> I've created a new table, where one of the columns is the user_id, which
> would be passed in to the function.
> Something like
> select * from my_new_table where user_id = ?
>
First of all, I would avoid passing in a user id if you can. If you can
pull the data out of the users table, that is better. Keep in mind, the
user's login is found in the SESSION_USER pseudo field, so if you can:
SELECT * FROM my_new_table t JOIN users u ON u.id = t.user_id WHERE
u.login=SESSION_USER;
that is far better.
Now for your stored procedure with arguments suppose you have something
like:
CREATE OR REPLACE FUNCTION my_extended_information(in_menu_class int)
returns setof extended_menu_items
language sql as
$$
select * from extended_menu_items where menu_class_id = $1;
$$;
You can then pull this information in with the following approaches:
$menu->exec_method(funcname => 'my_extended_information');
That will feed the http request param "menu_class" into the first argument
automatically.
If you want to make it explicit:
$menu->call_procedure(procname => 'my_extended_information', args =>
[$request->{menu_class]);
You can use $request as well as $menu with call_procedure.
Basically you use PostgreSQL functions as "named queries" and can use the
above two functions to pass arguments to them.
Best Wishes,
Chris Travers
>
>
> Thanks.
> Brian
>
> Brian Wolf
> Phone: 410.367.2958
> Email: [email protected]
> Try out Activus Secure Payments™, our recurring payments application.
> Demo at http://demo.activustech.com
> On 05/27/2013 10:14 PM, Chris Travers wrote:
>
> Can you be more specific?
>
> The general Perl object you probably want to use is
> LedgerSMB::DBObject::Menu and since this inherits from LedgerSMB::DBObject,
> the simplest way to do this is to create a user defined function in
> PostgreSQL. From there it is very straight-forward to pull that data into
> LedgerSMB as an array of hashrefs by using either $request->call_procedure
> if you want to use enumerated arguments, or $menu->exec_method if you want
> to use mapped arguments. In essence you can use a user defined function as
> a named query.
>
> For example, if I wanted to select some information for the current
> logged in user, I might:
>
> CREATE OR REPLACE FUNCTION custom_get_some_info()
> returns setof menu_item language sql as $$
>
> SELECT u.position, u.id, u.level, u.path,
> to_args(array[ua.attribute, ua.value))
> FROM user_menu u
> JOIN user_menu_attribute ua ON u.id = ua.node_id
> WHERE u.login = SESSION_USER;
> $$;
>
> Note that you can also RETURN TABLE(....) and this will work as
> advertised.
>
> Then from Perl you could add to a custom/menu.pl, the right namespace
> (LedgerSMB::Scripts::menu) and a modified version of the expanding_menu.pl.
> Unfortunately for now, the best thing to do would be to just copy/paste
> the function in there and then modify it. To call the procedure and get a
> list of hash refs it is as simple as:
>
> @new_menu_items = $request->call_procedure(procname =>
> 'custom_get_some_info');
>
> or alternatively
>
> @new_menu_items = $menu->exec_method(funcname => 'custom_get_some_info');
>
> If you have to pass additional info to the function it is a little more
> complex than this, but with more info I can provide more info on how to do
> that in the framework. The big difference between call_procedure (which
> works from either $request or $menu) is that it takes enumerated arguments,
> while exec_method maps in arguments from current object properties.
>
> Best Wishes,
> Chris Travers
>
>
> ------------------------------------------------------------------------------
> Try New Relic Now & We'll Send You this Cool Shirt
> New Relic is the only SaaS-based application performance monitoring service
> that delivers powerful full stack analytics. Optimize and monitor your
> browser, app, & servers with just a few lines of code. Try New Relic
> and get this awesome Nerd Life shirt! http://p.sf.net/sfu/newrelic_d2d_may
>
>
>
> _______________________________________________
> Ledger-smb-devel mailing
> [email protected]https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
>
>
>
>
> ------------------------------------------------------------------------------
> Introducing AppDynamics Lite, a free troubleshooting tool for Java/.NET
> Get 100% visibility into your production application - at no cost.
> Code-level diagnostics for performance bottlenecks with <2% overhead
> Download for free and get started troubleshooting in minutes.
> http://p.sf.net/sfu/appdyn_d2d_ap1
> _______________________________________________
> Ledger-smb-devel mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
>
>
------------------------------------------------------------------------------
Introducing AppDynamics Lite, a free troubleshooting tool for Java/.NET
Get 100% visibility into your production application - at no cost.
Code-level diagnostics for performance bottlenecks with <2% overhead
Download for free and get started troubleshooting in minutes.
http://p.sf.net/sfu/appdyn_d2d_ap1
_______________________________________________
Ledger-smb-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
