Re: Excel and postgresql functions - Mailing list pgsql-general

From Ivan Sergio Borgonovo
Subject Re: Excel and postgresql functions
Date
Msg-id 20090526132059.2a6f1586@dawn.webthatworks.it
Whole thread Raw
In response to Excel and postgresql functions  (Ivan Sergio Borgonovo <mail@webthatworks.it>)
List pgsql-general
On Tue, 26 May 2009 11:41:50 +0200
Ivan Sergio Borgonovo <mail@webthatworks.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


pgsql-general by date:

Previous
From: Ivan Sergio Borgonovo
Date:
Subject: Excel and postgresql functions
Next
From: Dimitri Fontaine
Date:
Subject: Re: How should I deal with disconnects during insert?