Variables in SQL scripts

["Machiel Richards" <machielr@xxxxxxxxx>]

Good day all

 

    I have posted a query on the Postgresql novice mailing list, however I think it might be more suitable here.

 

                Please see my request and information below:

 

 

 

               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?

 

 

 

 

                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.

 

 

 

 

 

 

 

 

Regards

Machiel