Additional info on request: Variables in SQL scripts

HI All

In addition to the information below relating to my query,
maybe this will help a bit more in providing the correct answers:

This is the information received from the guys who are
currently struggling with the reports, however I do not have enough
postgresql experience to assist.

herewith an example of the situation I explained to you earlier.

declare @clientID int

declare @dateFrom date

declare @dateTo date

declare @shipmentNumber varchar(50)

select @clientID = ?, @dateFrom = ?, @dateTo = ?, @shipmentNumber = ?

select s.ID as shipmentID, s.number as shipmentNumber, s.reference as
shipmentReference,

s.shippingMode as shipmentShippingMode, pol.description as portOfLoading,

pod.description as portOfDocking, d.name as division, sc.name as
saleCustomer,

i.incoterm as incoterm, c.ID as consignmentID, c.number as
consignmentNumber,

c.refNo as consignmentReference, c.shippingMode as consignmentShippingMode,
o.ID as orderID,

o.number as orderNumber, o.purchaseOrderReference as orderReference,
ili.invoiceID as invoiceID,

ili.ID as invoiceItemID, oli.ID as orderItemID, ili.itemReference as
itemReference,

ci.supplierReference as articleNumber, ili.itemDescription as description,

ili.unitQuantity as unitQty

from Shipment s, PortPlaceOfLoading pol, PortPlaceOfDocking pod, Invoice i,

InvoiceLineItem ili, CatalogueItem ci, LineItem oli, Orders o, Division d,

SaleCustomer sc, Consignment c

where s.clientID = @clientID

and s.costed = 1

and s.number like @shipmentNumber

and s.costedDate >= @dateFrom

and s.costedDate <= @dateTo

and pol.id = s.portOfLoadingID

and pod.id = s.portOfDockingID

and i.shipmentID = s.ID

and ili.invoiceID = i.ID

and ci.id = ili.catalogueItemID

and oli.ID = ili.lineItemID

and o.ID = oli.orderID

and d.ID = o.divisionID

and sc.ID = o.saleCustomerID

and c.id = o.consignmentID

and c.shippingMode != s.shippingMode

order by s.number, c.number, o.number, ili.itemReference

This is an example of a sybase query with variables declared within the
sybase sql language. A second query may also be executed within the same
session for example:

declare @clientID int

declare @dateFrom date

declare @dateTo date

select @clientID = ?, @dateFrom = ?, @dateTo = ?

select printDate = convert(varchar, getDate(), 111), dateRange =
convert(varchar, @dateFrom, 111) +' to ' + convert(varhar, @dateto, 111)

from Client c

where c.ID = @clientID

Also note that there is no guarantee that all queries will have the same
variables.

The task that I'm facing with is to get a postgres equivalent query. As far
as functions go for example convert(varchar,...,111) etc, I'm able to write
postgres equivalents but the declaration of variables is where I'm falling
short.

A possible solutions I've investigated so far though not the prefered nor
optimised is to create a temporary table with all variables used for this
report and then just use the relevant ones in the query. Another solution
would be to make use of prepared statements.

Do you have any other solutions we can investigate?

Any help would be appreciated.

HI all

I am looking for some more suggestions here on ways to use
variables in sql scripts to be run on postgresql

We have some Sybase reports that needs to be run against a
postgresql database using sql scripts.

For Sybase they set variables using the declare command for
instance :

Declare id int (just an example, not sure about the
syntax)

Then they refer to this same variable many times in more
than one query within this script.

From what I understand, the guys have a problem due to these
variable declarations not working. They did some investigations and they
stated that they found the possibility of using temporary tables to put the
variables in, however the temp table is only valid for the first transaction
so they are unable to use the variable a second time.

I did some googling as well and found something I tested using
a simple method:

- I created an sql script to set the variable Name DB

o \set DB <dbname>

o \c :DB

- I connected to postgresql using the postgres database and ran the
sql script which seemed to work fine as it then connected me to the
database.

However will this method work with the above situation as well or are there
other ways of doing this?

Regards

Machiel