Re: Excel and postgresql functions

On Tue, 26 May 2009 11:41:50 +0200
Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> wrote:

> I've built some functions just for encapsulation reasons and
> avoiding to pass the same parameter over and over.
>
> I'd like to access the result from Excel but it seems (and I'm not
> pretty sure it is the definitive answer) excel can suck data just
> coming from views and tables.
>
> - can someone confirm Excel/Access 2003 can't return result sets
> coming from functions?
> - if Excel/Access can't return results from a function is there a
> way to masquerade the function (accepting parameters) behind a
> table/view.

For all the people forced to fight with a software that has to be
tricked and not instructed...

The fault is Excel.

- Data -> Import External Data -> New Database Query
- Choose the odbc connection you created for Postgresql
- Don't add any query.
- Edit directly "SQL"
- save an easily "greppable" query (eg. select 'ermenegildo';)
- Ignore complaint.
- Close Microsoft Query.
- Say OK to "Import data" (just select a good "top left corner for
your table)

- Tools -> Macro -> Visual Basic Script Editor
- search your query and change it with any valid SQL, add ? if you
need parameters taken from cells
- save
- you'll be asked which cell contain the parameter (I didn't try to
use more than one parameter)

Now you can have an arbitrary query returned in an Excel sheet.

So yes... PostgreSQL can happily work with Excel.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it